如何在不使用子查询的情况下检索分组计数>1的行

需求 #

从abc表中提取按a列分组后，分组内记录数大于1的所有原始行，要求不使用子查询。

子查询实现参考（用户提供） #

如果允许使用子查询，对应的SQL语句如下：

SELECT *
FROM abc
LEFT JOIN 
    (SELECT a, COUNT(*) AS cnt
     FROM abc
     GROUP BY a
     HAVING COUNT(*) > 1) aa 
ON abc.a = aa.a
WHERE aa.a IS NOT NULL

示例数据表 #

预期查询结果 #

无查询解决方案 #

方案1：使用窗口函数（多数现代数据库支持）

利用窗口函数直接计算每个a对应的分组行数，再过滤出行数大于1的记录，写法简洁高效：

SELECT a, b
FROM (
    SELECT 
        a, 
        b, 
        COUNT(*) OVER (PARTITION BY a) AS cnt
    FROM abc
) t
WHERE cnt > 1

注：MySQL 8.0+、PostgreSQL、SQL Server等主流数据库均支持该语法。

方案2：自连接实现（无任何子查询）

通过表自连接匹配同一a值下不同b值的记录，再去重得到目标结果，兼容大部分旧版数据库：

SELECT DISTINCT abc1.a, abc1.b
FROM abc abc1
JOIN abc abc2 
  ON abc1.a = abc2.a 
  AND abc1.b != abc2.b

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