按最近的指定日期对行进行排序，但将过去的记录保存到结果集的末尾

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

我无法想象如何在SQL代码中实现这一点，但我觉得这是可能的。

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.

我想要的是将这些行排序到今天最近的日期(即2017-08-18)，并将以前具有regdate的行放在上面看到的结果集的末尾。

How can this be possible in SQL?

如何在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，仅此而已。

2 个解决方案

#1

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.

严格地说，第一个条件是没有必要的。然而，我认为这让逻辑更加清晰。

#2

Something like this:

是这样的:

order by case when REGDATE > curdate() then 1 else 2 end, regdate

#1