今天帮助同事调了一个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