mysql 排序去重 sql 写法

| data_id | user_id | data_name |hits
| 1 | 2 | test1 | 140
| 2 | 2 | test2 | 200
| 3 | 3 | test2 | 110
| 4 | 3 | test2 | 10
| 5 | 1 | test2 | 130
| 6 | 4 | test2 | 10
| 7 | 4 | test2 | 100

假如要查询出来下面的 3 条结果，也就是前 3 条点击量最多的数据， user_id 不能重复

| data_id | user_id | data_name |hits
| 2 | 2 | test2 | 200
| 5 | 1 | test2 | 130
| 3 | 3 | test2 | 110

sql 怎么写，再次谢谢！

Supplement 2 · Oct 21, 2016

感谢 @kfll @akira @Powered

最终用 @Powered 的写法查询成功，谢谢！

SELECT
	t.*
FROM
	(
		SELECT
			user_id,
			max(hits) AS max_hits
		FROM
			t
		GROUP BY
			user_id
	) t2
LEFT JOIN t ON t.user_id = t2.user_id
AND t.hits = t2.max_hits
ORDER BY
	t2.max_hits DESC
LIMIT 3

SQL

点击量

主键

data_name

8 replies • 2016-10-21 20:08:26 +08:00

cxbig

Oct 21, 2016 via iPhone

SELECT data_name, count(hits)
FROM table
GROUP BY data_name
ORDER BY count(hits) DESC

anai1943

Oct 21, 2016

@cxbig 您看下附言，我想查询的是这样的结果，谢谢！

lishunan246

Oct 21, 2016

select distinct

kfll

Oct 21, 2016 via iPhone

select * from (select user_id, max(hits) as max_hits from t group by user_id) t2 left join t on t.user_id = t2.user_id and t.hits = t2.max_hits

这样？

akira

Oct 21, 2016

@kfll
select t.* from (select user_id, max(hits) as max_hits from t group by user_id) t2 left join t on t.user_id = t2.user_id and t.hits = t2.max_hits

order by t.max_hits desc
limit 3

neoblackcap

Oct 21, 2016

MySQL 没有 windows 函数，无法实现你想要的效果，若是仅仅选取 user_id 跟 hits 两个 field 倒是可以做到

Powered

Oct 21, 2016

@akira

select t.* from (select user_id, max(hits) as max_hits from t group by user_id) t2 left join t on t.user_id = t2.user_id and t.hits = t2.max_hits

order by t2.max_hits desc
limit 3

reus

Oct 21, 2016

如果支持 window functions ，就很简单，好像 MariaDB 支持？ postgres 支持，所以很方便

select * from (

select
*, row_number() over (patition by user_id order by hits desc) as r
from table

)
where r = 1
order by hits desc
limit 8