日期列的 MySQL 条件 ORDER BY ASC/DESC
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/10626363/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
MySQL conditional ORDER BY ASC/DESC for date column
提问by Mike
I need a MySQL conditional ORDER BY statement for a datetime field. I have a table with posts which I would like to order in the following way: all future postsshould be ordered ASCand all historical postsordered DESC. Eg.:
我需要一个用于日期时间字段的 MySQL 条件 ORDER BY 语句。我有一个包含帖子的表格，我想按以下方式订购：所有未来的帖子都应该订购ASC并且所有历史帖子都订购DESC。例如。：
post_status post_date post_title
=========== ========= ==========
future 2012-10-01 Title 1
future 2012-12-01 Title 2
publish 2012-05-01 Title 3
publish 2012-01-01 Title 4
I need something similar to the following SQL...
我需要类似于以下 SQL 的东西...
SELECT post_status, post_date, post_title FROM wp_posts
WHERE post_status IN ('future', 'publish')
ORDER BY post_status ASC,
CASE post_status
WHEN 'future' THEN 'post_date ASC'
ELSE 'post_date DESC'
END;
Any hints on how to do this? Thanks!
有关如何执行此操作的任何提示？谢谢！
回答by rs.
Try this:
尝试这个：
ORDER BY post_status ASC,
CASE post_status WHEN 'future' THEN POST_DATE END ASC,
CASE WHEN post_status <> 'future' THEN post_date END DESC
回答by earth_tom
How about something like this? Select twice and union the results.
这样的事情怎么样？选择两次并合并结果。
Select * from (SELECT post_status, post_date, post_title
FROM wp_posts WHERE post_status IN ('future')
ORDER BY post_status ASC ) alias1
UNION
Select * from (SELECT post_status, post_date, post_title
FROM wp_posts WHERE post_status IN ('publish')
ORDER BY post_status DESC ) alias2
回答by Devart
Try this one -
试试这个——
SELECT
post_status, post_date, post_title
FROM
wp_posts
WHERE
post_status IN ('future', 'publish')
ORDER BY
IF(post_status = 'future', 0, 1),
IF(post_status = 'future', TO_DAYS(post_date), TO_DAYS(post_date) * -1);
回答by J-16 SDiZ
i would use an union all, tricks in order bycan't use index and is slower.
我会使用union all, 技巧order by无法使用索引并且速度较慢。
SELECT * FROM
((SELECT
1 AS a, @rownum:=@rownum+1 B, post_status, post_date, post_title
FROM
wp_posts, (SELECT @rownum:=0) r
WHERE
post_status='publish'
ORDER BY
post_date DESC)
UNION ALL
(SELECT
2 AS a, @rownum:=@rownum+1 B, post_status, post_date, post_title
FROM
wp_posts, (SELECT @rownum:=0) r2
WHERE
post_status='future'
ORDER BY
post_date)) ORDER BY A,B;
