MySQL的ORDER BY场加场

问题描述：

id type  left right 
1 featured 1  2 
2 default 3  1 
3 default 5  2 
4 default 2  7 
5 featured 3  4 
6 featured 3  2 
7 day  1  3 
8 default 12  42

我需要输出5个id其中type != day和sum(left + right)排序并通过功能排序，默认

首先，需要各方面的功能类型ORDERING by sum(left + right)，比type = dafule ordering by sum(left + right) LIMIT 5

我想什么：

5, 6, 1, 8, 4

谢谢！

您是否希望将结果作为单个字符串的ID排序，或者结果集或行是否正确。我明白回报集的顺序依据。 – DRapp 2012-02-08 12:51:20

答

排序由“精选”未来首先是IF（）的顺序由...如果类型是“精选”，然后用1作为排序依据，否则，使用2.既然你只有特色和默认可用（限制“日”条目）。否则，将改为一个CASE/WHEN结构考虑其他类型的

select 
     yt.id, 
     yt.type, 
     yt.left + yt.right as LeftPlusRight 
    from 
     YourTable yt 
    where 
     yt.type <> 'day' 
    order by 
     if(yt.type = 'featured', 1, 2), 
     LeftPlusRight DESC 
    limit 5

答

select id 
from your_table 
where `type` != 'day' 
order by `type`, sum(left + right) 
group by `type`  
limit 5

答

SELECT 
    ID 
FROM 
    yourTable 
WHERE 
    type <> 'day' 
ORDER BY (type = 'featured') DESC, (`left` + `right`) DESC 
LIMIT 5

上述查询给你正确的结果，我认为。

答

与预期结果：

5,6，1,8，4

你真正想要的ID通过 type递减由 left sum和 right降序排序，然后

，所以以下查询可满足您的需求：

SELECT 
    id 
FROM 
    tlr 
WHERE 
    `type`!='day' 
ORDER BY 
    `type` DESC, `left`+`right` DESC 
LIMIT 5;

它的工作原理是这样的：

mysql [localhost] {msandbox} (test) > select * from tlr; 
+----+----------+------+-------+ 
| id | type  | left | right | 
+----+----------+------+-------+ 
| 1 | featured | 1 |  2 | 
| 2 | default | 3 |  1 | 
| 3 | default | 5 |  2 | 
| 4 | default | 2 |  7 | 
| 5 | featured | 3 |  4 | 
| 6 | featured | 3 |  2 | 
| 7 | day  | 1 |  3 | 
| 8 | default | 12 | 42 | 
+----+----------+------+-------+ 
8 rows in set (0.00 sec) 

mysql [localhost] {msandbox} (test) > select id from tlr where `type`!='day' order by type desc, `left`+`right` desc limit 5; 
+----+ 
| id | 
+----+ 
| 5 | 
| 6 | 
| 1 | 
| 8 | 
| 4 | 
+----+ 
5 rows in set (0.00 sec)

MySQL的ORDER BY场加场

相关推荐