按行最接近指定日期排序，但将过去的记录放到结果集的末尾(Sort rows by nearest to specified date but put records in the past to th

按行最接近指定日期排序，但将过去的记录放到结果集的末尾(Sort rows by nearest to specified date but put records in the past to the end of result set)

我无法想象如何在SQL代码中完成，但我觉得这是可能的。

我有以下记录：

ID | NAME | REGDATE 1 sam 2017-08-12 2 gab 2017-08-13 3 mab 2017-08-19 4 mab 2017-08-20 5 don 2017-08-18 6 kob 2017-08-14 7 mol 2017-08-15

现在我想对上面的行进行排序，如下所示：

ID | NAME | REGDATE 5 don 2017-08-18 3 mab 2017-08-19 4 mab 2017-08-20 7 mol 2017-08-15 6 kob 2017-08-14 2 gab 2017-08-13 1 sam 2017-08-12

我想要的是将行排序到今天最近的日期（2017-08-18）并将过去有regdate的行放在结果集的末尾，您可以在上面看到。

如何在SQL中实现这一点？

我找不到合适的术语如何在搜索引擎中搜索答案。 我所知道的就是使用order by ，就是这样。

I just can't imagine how can this be done in SQL code but I feel it's possible.

I have the following records:

ID | NAME | REGDATE 1 sam 2017-08-12 2 gab 2017-08-13 3 mab 2017-08-19 4 mab 2017-08-20 5 don 2017-08-18 6 kob 2017-08-14 7 mol 2017-08-15

Now I want to sort the rows above like this:

ID | NAME | REGDATE 5 don 2017-08-18 3 mab 2017-08-19 4 mab 2017-08-20 7 mol 2017-08-15 6 kob 2017-08-14 2 gab 2017-08-13 1 sam 2017-08-12

What I want is to sort the rows to the nearest date today (which is 2017-08-18) and put the rows that has a regdate in the past in the end of the result set which you can see above.

How can this be possible in SQL?

I can't find the right term how can I search for an answer in the search engine. All I know is to use order by and that's it.

最满意答案

您可以通过使用order by多个键来执行此操作：

order by ( regdate >= curdate() ) desc, -- put current and future first (case when regdate >= curdate() then regdate end) asc, regdate desc

严格来说，第一个条件是没有必要的。 但是，我认为它使逻辑更清晰。

You can do this by using multiple keys for the order by:

order by ( regdate >= curdate() ) desc, -- put current and future first (case when regdate >= curdate() then regdate end) asc, regdate desc

Strictly speaking, the first condition is not necessary. However, I think it makes the logic clearer.