mysql 排序去重 sql 写法

[anai1943]

表结构如下

data_id 主键

user_id

data_name

hits 点击量

每个用户有多条数据，查询前 8 个用户点击量最多的一条数据，再按照点击量倒序，这个 sql 怎么写，谢谢！

[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