我有像桌子
test
+---------------------+-------+
| date_in | title |
+---------------------+-------+
| 2018-01-01 00:00:00 | foo |
| 2018-01-02 00:00:00 | bar |
| 2018-01-03 00:00:00 | man |
| 2018-01-04 00:00:00 | foo |
| 2018-01-05 00:00:00 | test |
+---------------------+-------+
我想要的结果是
+---------------------+-------+
| date_in | title |
+---------------------+-------+
| 2018-01-05 00:00:00 | test |
| 2018-01-04 00:00:00 | foo | -- see this
| 2018-01-01 00:00:00 | foo | -- see this
| 2018-01-03 00:00:00 | man |
| 2018-01-02 00:00:00 | bar |
+---------------------+-------+
我已经在下面的查询和其他几个查询中尝试过,但是它不起作用
select * from test order by title, date_in desc
解决方法:
您尝试按三列订购.
>每个标题首次出现的date_in.
>标题本身
>每行的date_in
您需要在查询中生成这三个中的第一个.为此,您编写一个子查询并将其(好像它是一个虚拟表)连接到表中.
select min(date_in) firstdate,
title
from test
group by title
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
如果子查询很慢,请尝试在(title,date_in)上创建索引.这种查询使用的是所谓的loose index scan,并且应该相当快.
ALTER TABLE test ADD INDEX test_title_date (title,date_in);
标签:mysql
来源: https://codeday.me/bug/20191108/2009075.html