mysql 排序去重 sql 写法

[anai1943]

表结构如下

data_id 主键

user_id

data_name

hits 点击量

每个用户有多条数据，查询前 8 个用户点击量最多的一条数据，再按照点击量倒序，这个 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 怎么写，再次谢谢！

感谢 @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

[cxbig]

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

[anai1943]

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

[lishunan246]

select distinct

[kfll]

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]

@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]

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

[Powered]

@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]

如果支持 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