mysql - SQL:首先显示 future 的记录，然后是其余的。均按 create_date DESC

Question

我有这样的表:

id  | create_date | start_date | status
112 | 2015-03-13  | 2015-03-14 |   1
106 | 2015-03-07  | 2015-03-19 |   2
105 | 2015-03-06  | 2015-03-29 |   1
104 | 2015-03-05  | 2015-03-29 |   1
101 | 2015-03-03  | 2015-03-20 |   1
100 | 2015-03-01  | 2015-03-03 |   1
 99 | 2015-03-01  | 2015-03-06 |   2

现在，我想通过以下方式选择和排序这些记录:

1. 记录 status = 1和 start_date >= NOW()必须排在第一位，按 create_date DESC 排序- 最新记录 status = 1 and start_date >= now()将是第一个。这些是“活跃的”

2. 其他记录(表示带 status = 0 , status = 2 和 status = 1 AND start_date < NOW() 的记录)会在 active 后面，按 create_date DESC 排序.

预期输出:

id  | create_date | start_date | status
112 | 2015-03-13  | 2015-03-14 |   1
105 | 2015-03-06  | 2015-03-29 |   1
104 | 2015-03-05  | 2015-03-29 |   1
101 | 2015-03-03  | 2015-03-20 |   1
106 | 2015-03-07  | 2015-03-19 |   2
100 | 2015-03-01  | 2015-03-03 |   1
 99 | 2015-03-01  | 2015-03-06 |   2 -- behind id 100 not because of higher status, but because of create_date (I store datetime instead of date).

SQL查询:

SELECT a.[columns],
       d.[columns],
       r1.[columns],
       r2.[columns]
FROM articles a
JOIN reg r1
    ON r1.id = a.locality_id
LEFT JOIN reg r2
    ON r2.id = r1.parent_id
LEFT JOIN `d` AS d
    ON d.id = a.demander_id
WHERE a.s_id = 1
ORDER BY (a.status = 1 AND a.start_date >= NOW()) DESC,
         a.create_date DESC

当前输出:

id  | create_date | start_date | status
105 | 2015-03-06  | 2015-03-29 |   1
104 | 2015-03-05  | 2015-03-29 |   1
101 | 2015-03-03  | 2015-03-20 |   1
112 | 2015-03-13  | 2015-03-14 |   1
106 | 2015-03-07  | 2015-03-19 |   2
100 | 2015-03-01  | 2015-03-03 |   1
 99 | 2015-03-01  | 2015-03-06 |   2

它用 status = 1 返回给我 future 的记录在另一个之前，没关系，但按start_date DESC排序.

这DESC应该意味着“按条件排序，在该条件之前返回 1 或 0 desc(条件为真时的第一个记录，而不是其余记录)，然后按创建日期从最新记录排序”。但它还有其他作用。

有什么想法吗？

Answer 1

你很接近，但你需要与 CURRENT_DATE() 而不是 NOW() 进行比较:

ORDER BY CASE
    WHEN status = 1 AND start_date >= CURRENT_DATE() THEN 1
    ELSE                                                  2
END, create_date DESC

如果当前日期时间是 2015-03-14 00:00:01 那么:

• 2015-03-14 >= NOW() 不匹配
• 2015-03-14 >= CURRENT_DATE() 匹配