mysql - 如何按大于 DATE(NOW()) 的日期列排序？

Question

我有 date_end 列，我需要对日期大于今天的顶部行进行排序。

所以给日期大于今天=“1”，小于=“0”并对其进行排序。

所以用下面的数据表：

+----+------------+ 
| id | date_end   |  
+----+------------+ 
| 1  | 31-01-2018 |  
+----+------------+ 
| 2  | 01-05-2019 |  
+----+------------+ 
| 3  | 31-05-2018 |  
+----+------------+ 
| 4  | 31-07-2019 |  
+----+------------+ 
| 5  | 31-04-2020 |  
+----+------------+ 
| 6  | 31-08-2019 |  
+----+------------+ 

我需要虚拟添加“实际”列并按其排序：

+----+------------+--------+
| id | date_end   | actual |
+----+------------+--------+
| 1  | 31-01-2018 | 0      |
+----+------------+--------+
| 2  | 01-05-2019 | 0      |
+----+------------+--------+
| 3  | 31-05-2018 | 0      |
+----+------------+--------+
| 4  | 31-07-2019 | 1      |
+----+------------+--------+
| 5  | 31-04-2020 | 1      |
+----+------------+--------+
| 6  | 31-08-2019 | 1      |
+----+------------+--------+

类似：ORDER BY CASE WHEN date_end> DATE(NOW()) THEN 1 ELSE 2 END

标签： mysqlsql

Answer 1

然后完全按照您的建议进行操作：

ORDER BY CASE WHEN date_end > curdate() THEN 1 ELSE 0 END