mysql按时间排序不正确_mysql-按日期时间排序,但彼此之间保留相同标题的记录

我有像桌子

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