postgre如何根据一列的多个最小值在sql中选择多行

Question

我有以下SQL查询，查询结果如下所示。如何根据cnt列选择多个最小值？

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

上述查询的输出

"id"    "cnt"
1003    3
1008    1
1011    2
1017    1

我希望输出是

"id"    "cnt"
1008    1
1017    1

感谢您的帮助。非常感谢。

Answer 1

您可以使用必须检查

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 a.id , COUNT(a.id) cnt
from table1 a
where a.id in (SELECT id from table2 WHERE name = 'abc') 
GROUP BY a.id) as latest)

看看这个数据库小提琴

Answer 2

您可以使用Having来获取Min计数。

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(id) = (SELECT MIN(cnt)                              
FROM (SELECT COUNT(a.id) cnt
FROM table1 a
WHERE a.id in (SELECT id from table2 WHERE name = 'abc') 
GROUP BY a.id) b);