如何编写SQL查询获取各部门（dep）中num值Top2的id记录

示例数据 #

id    dep num
288610  101 95
334028  101 64
480492  101 61
259007  102 215
333655  102 177
369079  102 146
128672  102 103
398319  103 247
384462  103 222
448798  103 204
430841  103 133
387829  103 62

当前使用的查询语句 #

select  a.id, a.dep, COUNT(b.units) as num
from id a
  JOIN dev b ON(a.id = b.id)
where  b.units is null
group by a.id, a.dep
having COUNT(a.enota) >10
order by  a.dep, COUNT(b.units) desc

期望输出结果 #

id  dep num
288610  101 95
334028  101 64
259007  102 215
333655  102 177
398319  103 247
384462  103 222

解决方案 #

要实现按部门筛选num值排名前2的记录，窗口函数ROW_NUMBER()是最优方案，它能在部门分组内对记录按num降序编号，之后筛选编号≤2的记录即可。

适配你现有查询逻辑的最终SQL #

SELECT id, dep, num
FROM (
    SELECT 
        a.id, 
        a.dep, 
        COUNT(b.units) AS num,
        -- 按部门分组，对每个组内的记录按num降序编号
        ROW_NUMBER() OVER (PARTITION BY a.dep ORDER BY COUNT(b.units) DESC) AS rn
    FROM id a
    JOIN dev b ON a.id = b.id
    WHERE b.units IS NULL
    GROUP BY a.id, a.dep
    HAVING COUNT(a.enota) > 10
) ranked_data
WHERE rn <= 2
ORDER BY dep, num DESC;

逻辑说明 #

• PARTITION BY a.dep：将数据按部门拆分，确保每个部门内部独立计算排名
• ORDER BY COUNT(b.units) DESC：在每个部门内，按你计算得到的num值从大到小排序
• ROW_NUMBER()：为每个部门内的记录分配唯一排名编号，从1开始
• 外层查询筛选rn <= 2，保留每个部门排名前2的记录

如果你是直接从示例数据那样的表中查询（而非通过关联分组计算），可以简化为：

SELECT id, dep, num
FROM (
    SELECT 
        id, 
        dep, 
        num,
        ROW_NUMBER() OVER (PARTITION BY dep ORDER BY num DESC) AS rn
    FROM your_table_name -- 替换为实际表名
) ranked_data
WHERE rn <= 2
ORDER BY dep, num DESC;

内容的提问来源于stack exchange，提问作者Phantom