ORDER BY日期，过去日期之后的过去日期 [英] ORDER BY date with past dates after upcoming dates

问题描述

我需要在MySql数据库中的表上执行查询，其中结果行的顺序将如下所示:

I need do execute a query on a table in a MySql database where the order of the resulting rows will be like this:

如果今天是2012年10月9日:

If today is 10/09/12:

...
11/09/12 
12/09/12
15/09/12
08/09/12  <--here start the past dates
07/09/12
05/09/12
....

有没有一种方法可以直接在MySQL中实现?

Is there a way to achive this directly in MySQL?

我已经通过以下方式解决:

首先，select语句包含一个新的布尔值，该布尔值标记日期是过去还是将来的日期:

First, the select statement include a new boolean that mark if the date is past or future by:

SELECT DISTINCT *,CASE  WHEN startdate < CURDATE() THEN 0
                ELSE 1 END AS past_or_future 

第二，我做了一个双重的排序依据":首先在past_or_future布尔值上，然后在日期上，条件是这样的:

Second, I've done a double 'Order by': first on the past_or_future boolean and then on the date, with the conditional like this:

ORDER BY past_or_future  DESC , CASE WHEN past_or_future  = 1 THEN startdate END ASC, CASE WHEN past = 0 THEN startdate END DESC

通过这种方式，我首先获得了按日期排序的所有即将到来的日期(从较低的值到较高的值)，然后是从日期排序的所有过去的日期(从较高的值到较低的值)

in this way I've obtained for first all the upcoming dates ordered by date (from the lower value to higher) then all the past dates ordered from the date (from the higher to the lower)

推荐答案

即使在ORDER BY子句中，您仍然可以执行CASE语句，

You can still do CASE statement even in ORDER BY clause,

SELECT *
FROM tableName
ORDER BY (CASE WHEN DATE(dateColumn) < DATE(GETDATE())
              THEN 1
              ELSE 0
         END) DESC, dateColumn ASC

这篇关于ORDER BY日期，过去日期之后的过去日期的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！