如何在MySQL / Mariadb中实现这一点 - 排序和排序

Question

---------------------------
|Status  |Application Date|
|New     |2019-01-02      |
|New     |2019-01-01      |
|Updated |2019-01-15      |
|Deleted |2019-01-20      |
|Updated |2019-01-16      |
---------------------------
1. Sort in-order from New,Updated, and Deleted
a. Sort by New ASC - to see the first entry for first come first serve
b. Sort by Updated Desc - to see the latest update first
c. Sort by Deleted Desc - see the latest deleted

我已经尝试了 3 个查询和联合，但您可以将它们一起排序，而不是每个查询。

Answer 1

试试这个逻辑：

SELECT *
FROM yourTable
ORDER BY
FIELD(Status, 'New', 'Updated', 'Deleted'),
CASE WHEN Status = 'New' THEN UNIX_TIMESTAMP(app_date) ELSE -1.0*UNIX_TIMESTAMP(app_date) END;

第一级排序将新记录放在更新的记录之前，将更新的记录放在删除的记录之前。 对于新记录，第二级按日期升序排序，对所有其他记录按降序排序。