如何在SQL中基于cnt列的多个最小值筛选多行数据

要筛选出cnt列等于最小值的记录，有以下几种可行的SQL写法：

方法1：嵌套子查询获取最小值 #

先计算所有符合条件的id的计数，再从中取出最小值，最后筛选计数等于该最小值的记录：

SELECT a.id, COUNT(a.id) cnt
FROM table1 a
WHERE a.id IN (SELECT id FROM table2 WHERE name = 'abc')
GROUP BY a.id
HAVING COUNT(a.id) = (
    SELECT MIN(cnt)
    FROM (
        SELECT COUNT(id) cnt
        FROM table1
        WHERE id IN (SELECT id FROM table2 WHERE name = 'abc')
        GROUP BY id
    ) t
);

方法2：使用CTE（公共表表达式）简化逻辑 #

先通过CTE生成所有id的计数结果，再从中筛选出计数等于最小值的记录：

WITH id_counts AS (
    SELECT a.id, COUNT(a.id) cnt
    FROM table1 a
    WHERE a.id IN (SELECT id FROM table2 WHERE name = 'abc')
    GROUP BY a.id
)
SELECT id, cnt
FROM id_counts
WHERE cnt = (SELECT MIN(cnt) FROM id_counts);

方法3：窗口函数（推荐，效率更高） #

利用窗口函数MIN() OVER()直接在分组计算时获取全局最小计数，一步完成筛选：

SELECT id, cnt
FROM (
    SELECT 
        a.id, 
        COUNT(a.id) cnt,
        MIN(COUNT(a.id)) OVER () min_cnt
    FROM table1 a
    WHERE a.id IN (SELECT id FROM table2 WHERE name = 'abc')
    GROUP BY a.id
) t
WHERE cnt = min_cnt;

以上三种写法都能得到你想要的输出：

"id"    "cnt"
1008    1
1017    1

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