我有类似表格
+---------------------+-------+
| date_in | title |
+---------------------+-------+
| 2018-01-01 00:00:00 | foo2 |
| 2018-01-02 00:00:00 | bar |
| 2018-01-03 00:00:00 | man |
| 2018-01-04 01:00:00 | foo2 |
| 2018-01-04 02:00:00 | foo |
| 2018-01-04 03:00:00 | foo1 |
| 2018-01-04 04:00:00 | foo2 |
| 2018-01-05 00:00:00 | test |
+---------------------+-------+
我想要的结果是
+---------------------+-------+
| date_in | title |
+---------------------+-------+
| 2018-01-05 00:00:00 | test |
| 2018-01-04 04:00:00 | foo2 | -- see this
| 2018-01-04 01:00:00 | foo2 | -- see this
| 2018-01-04 03:00:00 | foo1 |
| 2018-01-04 02:00:00 | foo |
| 2018-01-03 00:00:00 | man |
| 2018-01-02 00:00:00 | bar |
| 2018-01-01 00:00:00 | foo2 |
+---------------------+-------+
我已经尝试了下面的查询和其他几个查询,但它不起作用
SELECT
detail.*
FROM
test detail
JOIN ( SELECT min( date_in ) firstdate, title FROM test GROUP BY title ) firstdate ON detail.title = firstdate.title
ORDER BY
firstdate.firstdate,
detail.title,
detail.date_in
但是此查询显示具有相同标题的行,而不考虑日期
我希望行仅在同一天
显示这里 http://sqlfiddle.com/#!9/039aa/6 带有架构的 SQLFiddle。
似乎您需要按日期(date_in(和标题描述排序
SELECT
detail.*
FROM test detail
ORDER BY
date(detail.date_in),
detail.title desc,
detail.date_in
我认为您实际上想按每个日期的最晚时间对标题进行排序。 在 MySQL 8+ 中,您可以执行以下操作:
SELECT d.*
FROM test d
ORDER BY date(d.date_in),
MAX(d.date_in) OVER (PARTITION BY date(d.date_in), d.title),
d.title desc,
d.date_in ;
在早期版本中,可以使用子查询。 这是一种方法:
SELECT d.*
FROM test d
ORDER BY date(d.date_in),
(SELECT MAX(d2.date_in) FROM test d2 WHERE d2.title = d.title AND DATE(d2.date_in) = DATE(d.date_in)),
d.title desc,
d.date_in;
或者,您可以使用显式JOIN
:
SELECT d.*
FROM test d JOIN
(SELECT d2.title, DATE(d2.date_in) as date, MAX(d2.date_in) as max_date_in
FROM test d2
GROUP BY d2.title, DATE(d2.date_in)
) d2
ON d2.title = d.title AND d2.date = DATE(d.date_in)
ORDER BY date(d.date_in),
d2.max_date_in,
d.title desc,
d.date_in
试试这个:
SELECT
detail.*
FROM
test detail
JOIN ( SELECT min( date_in ) firstdate, title FROM test GROUP BY title ) firstdate ON detail.title = firstdate.title
ORDER BY
DATE(detail.date_in),
detail.title;
从ORDER BY
中删除了firstdate.firstdate
和编辑DATE(detail.date_in)
。