ORDER BY DISTINCT LIMIT 同时使用？

Question

表字段：user_id,keyword,query_date

现在想实现查询某个user_id按时间排序的前五个keyword。由于keyword是可以重复的，我的SQL如下：

SELECT DISTINCT
  t.keyword,
  t.query_date
FROM t_query_keyword t
WHERE t.user_id = '748761E1-260F-4E61-9D0D-BE6D122560E2'
ORDER BY t.query_date DESC
LIMIT 5;

结果没有实现keyword的去重

请问这个sql该怎么写？

Answer 1

DISTINCT 针对的是select 后面的所有字段， 如果你想keyword不重复，但对query_date不care, 你可以只取keyword, 如果你想取keywork和相应的最大日期的query_date,可以用group by... having ....

如：

drop table t_query_keyword;
create table t_query_keyword(
    user_id varchar(38) ,
    keyword varchar(100),
    query_date date
);


insert into t_query_keyword (user_id, keyword, query_date)
values ('748761E1-260F-4E61-9D0D-BE6D122560E2', 'java',  '2018-01-01'),
('748761E1-260F-4E61-9D0D-BE6D122560E2', 'sql',  '2018-01-02'),
('748761E1-260F-4E61-9D0D-BE6D122560E2', 'sql',  '2018-01-03'),
('748761E1-260F-4E61-9D0D-BE6D122560E2', 'sql',  '2018-01-03'),
('748761E1-260F-4E61-9D0D-BE6D122560E2', 'js',  '2018-01-04'),
('748761E1-260F-4E61-9D0D-BE6D122560E2', 'html',  '2018-01-05'),
('748761E1-260F-4E61-9D0D-BE6D122560E5', 'docker',  '2018-01-06'),
('748761E1-260F-4E61-9D0D-BE6D122560E2', 'kvm',  '2018-01-07');

SELECT DISTINCT
  t.keyword,
  t.query_date
FROM t_query_keyword t
WHERE t.user_id = '748761E1-260F-4E61-9D0D-BE6D122560E2'
ORDER BY t.query_date DESC
LIMIT 6;

返回的结果是：

+---------+------------+
| keyword | query_date |
+---------+------------+
| kvm     | 2018-01-07 |
| html    | 2018-01-05 |
| js      | 2018-01-04 |
| sql     | 2018-01-03 |
| sql     | 2018-01-02 |
| java    | 2018-01-01 |
+---------+------------+
6 rows in set (0.00 sec)

keyword=sql, query_date=2018-01-03有两条，但因为DISTINCT, 只返回其中一条。

或者用如下的sql, 可以保证keyword不重复。

SELECT 
  keyword, max(query_date) as query_date
FROM t_query_keyword
WHERE user_id = '748761E1-260F-4E61-9D0D-BE6D122560E2'
group by keyword
ORDER BY query_date DESC
LIMIT 5;

返回的结果是

+---------+------------+
| keyword | query_date |
+---------+------------+
| kvm     | 2018-01-07 |
| html    | 2018-01-05 |
| js      | 2018-01-04 |
| sql     | 2018-01-03 |
| java    | 2018-01-01 |
+---------+------------+
5 rows in set (0.00 sec)