MySQL排序依据字段加字段

Question

我有一张桌子

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

我需要输出五个id中的type != day，并按sum(left + right)排序，按featured，default排序

首先，与type = dafule ordering by sum(left + right) LIMIT 5相比，需要所有特性类型的ORDERING by sum(left + right)

我想得到的是：

5, 6, 1, 8, 4

谢谢!

Answer 1

按“特色”排序首先出现的是order by中的IF() ...如果类型是" featured "，则使用1作为排序基础，否则使用2。因为您只有featured和default可用(限制"day“条目)。否则，它将被更改为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

Answer 2

得到了预期的结果：

5、6、1、8、4

实际上，您希望按type desc排序id，然后按sum of left和right desc排序，因此以下查询可能满足您的需要：

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)

Answer 3

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