如何先按新的日期排序，然后按过去的日期排序 - How sort by new date first then by passed date?

How sort by new date first then by passed date?

我在mysql数据库中有日期列。我需要按日期排序ASC，但首先显示尚未发生的日期，然后是发生的日期。如何做sql语句?结果示例:

for date 25 October 2015
new by ASC:
2015-10-25
2015-10-27
2015-11-03
...
then old by DESC:
2015-10-24
2015-10-14
2015-09-10
...

我使用的是Yii2框架(但干净的php也会很好)。

给定这个示例数据:

CREATE TABLE t
    (`date` date)
;
INSERT INTO t
    (`date`)
VALUES
    ('2015-10-25'),
    ('2015-10-27'),
    ('2015-11-03'),
    ('2015-10-24'),
    ('2015-10-14'),
    ('2015-09-10')
;

和这个查询:

SELECT
date
FROM
t
ORDER BY
date >= CURDATE() DESC
, ABS(DATEDIFF(date, CURDATE()));

你得到你想要的输出:

|                        date |
|-----------------------------|
|   October, 25 2015 00:00:00 |
|   October, 27 2015 00:00:00 |
|  November, 03 2015 00:00:00 |
|   October, 24 2015 00:00:00 |
|   October, 14 2015 00:00:00 |
| September, 10 2015 00:00:00 |

查看它在sqlfiddle中工作

检查完fancyPants的解决方案后，我觉得很傻。

但是我想展示我正在处理的那个

SqlFiddleDemo

  SELECT *
  FROM (
        SELECT t.*,
               (@rn := if(true, @rn + 1, 1)
               ) as rn
        FROM myTable t         
        CROSS JOIN
              (select @rn := 0) params
        WHERE dateField >= NOW()
        ORDER BY dateField asc
      ) X
  UNION 
  SELECT *
  FROM (
        SELECT t.*,
                 (@bn := if(true, @bn + 1, 1)
                 ) as rn
        FROM myTable t         
        CROSS JOIN
              (select @bn := (SELECT COUNT(*) 
                              FROM myTable 
                              WHERE dateField >= NOW()
                             ) a
              ) params
        WHERE dateField < NOW()
        ORDER BY dateField desc
      ) Y

|                   dateField | rn |
|-----------------------------|----|
|   October, 25 2015 00:00:00 |  1 |
|   October, 27 2015 00:00:00 |  2 |
|  November, 03 2015 00:00:00 |  3 |
|   October, 24 2015 00:00:00 |  4 |
|   October, 14 2015 00:00:00 |  5 |
| September, 10 2015 00:00:00 |  6 |