mysql 查询排序并去重_【mysql】数据按照时间排序并且去重

今天帮助同事调了一个SQL语句。需求是这个样子滴：查询最近12条搜索的图书记录，并且去重。

数据库现在的数据是这个样子的。

他写的SQL语句查询出来是这个样子的：

虽然时间是降序排序的，但是图书的记录不是最新搜索的在前面

更改后的SQL是这个样子的：

-- 修改之前的语句

SELECT

DISTINCT

ts.book_basic_id

FROM

tl_user_search_record ts

WHERE

ts.is_delete = 0

AND ts.user_id = '1071009104044093400'

ORDER BY ts.create_time DESC

LIMIT 12

SELECT *

FROM

tl_user_search_record ts

WHERE

ts.is_delete = 0

AND ts.user_id = '1071009104044093400'

GROUP BY ts.book_basic_id

ORDER BY

ts.create_time DESC

LIMIT 12

---------------------------------------------------------------------------------

-- 修改之后的

SELECT

*

FROM

( SELECT * FROM tl_user_search_record ts WHERE ts.is_delete = 0 AND ts.user_id = '1071009104044093400' ORDER BY ts.create_time DESC ) a

GROUP BY

a.book_basic_id

ORDER BY

a.create_time DESC

LIMIT 12

SELECT DISTINCT

a.book_basic_id

FROM

( SELECT * FROM tl_user_search_record ts WHERE ts.is_delete = 0 AND ts.user_id = '1071009104044093400' ORDER BY ts.create_time DESC ) a

LIMIT 12