ORDER BY日期，过去日期之后的过去日期

本文介绍了ORDER BY日期，过去日期之后的过去日期的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！ 问题描述

我需要在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