日期列的MySQL条件ORDER BY ASC/DESC [英] MySQL conditional ORDER BY ASC/DESC for date column

问题描述

我需要一个MySQL条件ORDER BY语句用于datetime字段.我有一张桌子，上面有一些帖子，我想通过以下方式对其进行排序:所有未来帖子都应按 ASC 进行排序，而所有历史帖子应进行排序 DESC .例如:

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 posts should be ordered ASC and all historical posts ordered DESC. Eg.:

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

我需要类似于以下SQL ...

I need something similar to the following 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!

推荐答案

尝试一下:

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

这篇关于日期列的MySQL条件ORDER BY ASC/DESC的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！