英文:
how to achieve this in mysql/mariadb - Order and Sorting
问题
|状态 |申请日期 |
|新建 |2019-01-02 |
|新建 |2019-01-01 |
|已更新 |2019-01-15 |
|已删除 |2019-01-20 |
|已更新 |2019-01-16 |
- 按照状态排序，顺序为新建、已更新和已删除
a. 按新建升序排序 - 查看首次提交的第一条记录
b. 按已更新降序排序 - 查看最新的更新记录
c. 按已删除降序排序 - 查看最新的删除记录
我尝试过使用3个查询和联合操作，但您可以将它们一起排序，而不是按查询排序。
英文:
---------------------------
|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
I have tried 3 query and union but you can order them together not per query.
答案1
得分: 4
尝试这个逻辑：
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;
第一级排序将新记录放在更新记录之前，将更新记录放在删除记录之前。第二级对新记录按日期升序排序，对其他所有记录按日期降序排序。
英文:
Try this logic:
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;
The first level of sorting places new records before updated records, and updated records before deleted records. The second level sorts ascending on the date for new records, and descending for all others.
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论