SQL如何基于id出现次数返回出现最多的前n个id对应数据
SQL查询获取出现次数最多的前N个ID对应所有行的实现方案
假设你的表名为your_table,实际使用时替换为自己的表名即可,下文的n为你要获取的前N个ID的数值,按需替换即可。
全数据库通用写法
所有关系型数据库都兼容的实现方案:
SELECT t.* FROM your_table t INNER JOIN ( -- 子查询先统计每个ID出现次数,倒序排序后取前N个ID SELECT id FROM your_table GROUP BY id ORDER BY COUNT(*) DESC LIMIT n ) top_n_ids ON t.id = top_n_ids.id
逻辑说明:先通过子查询拿到出现次数排前N的ID集合,再和原表关联匹配,即可拿到这些ID对应的所有行数据。
窗口函数写法(适配并列排名场景)
如果你需要处理ID出现次数相同的并列场景,比如2个ID出现次数一致并列第2,需要都计入前2个的统计范围,更推荐使用该写法,支持MySQL8.0+、PostgreSQL、Oracle、SQL Server等支持窗口函数的数据库:
WITH id_rank AS ( SELECT id, -- 按ID出现次数倒序排名,相同次数排名一致 DENSE_RANK() OVER(ORDER BY COUNT(*) DESC) AS rank_num FROM your_table GROUP BY id ) SELECT t.* FROM your_table t INNER JOIN id_rank r ON t.id = r.id WHERE r.rank_num <= n
效果验证
以你提供的示例数据为例,各ID出现次数统计结果:
- id=1:3次
- id=2:2次
- id=3:1次
- id=4:1次
取n=1时,返回id=1对应的3行全部数据;取n=2时,返回id=1、id=2对应的5行全部数据,和需求要求的效果完全匹配。
内容的提问来源于stack exchange,提问作者yyz_vanvlet
相关产品推荐
相关产品推荐

