mysql order by 日期,日期列的MySQL条件ORDER BY 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

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!

解决方案

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