sql - 相互比较 SQL 组

Question

与其他组相比，如何仅针对满足某些标准的那些组过滤分组结果集？例如，只有那些具有最大组成记录数的组？

我曾认为如下子查询应该可以解决问题:

SELECT * FROM (
    SELECT   *, COUNT(*) AS Records
    FROM     T
    GROUP BY X
) t HAVING Records = MAX(Records);

然而最后的 HAVING的添加子句导致一个空的记录集...这是怎么回事？

Answer 1

在 MySQL 中(我假设你正在使用它，因为你已经发布了 SELECT *, COUNT(*) FROM T GROUP BY X 在我知道的所有 RDBMS 中都会失败)。您可以使用:

SELECT  T.*
FROM    T
        INNER JOIN
        (   SELECT  X, COUNT(*) AS Records
            FROM    T
            GROUP BY X
            ORDER BY Records DESC
            LIMIT 1
        ) T2
            ON T2.X = T.X

这已经在 MySQL 中进行了测试，并删除了隐式分组/聚合。

如果您可以使用窗口函数和带有 Ties 或 Common Table 表达式的 TOP/LIMIT 之一，它会变得更短:

窗口函数 + CTE: (MS SQL-Server & PostgreSQL 测试)

WITH CTE AS
(   SELECT  *, COUNT(*) OVER(PARTITION BY X) AS Records
    FROM    T
)
SELECT  *
FROM    CTE
WHERE   Records = (SELECT MAX(Records) FROM CTE)

带 TOP 的窗口函数 (MS SQL-Server 测试)

SELECT  TOP 1 WITH TIES *
FROM    (   SELECT  *, COUNT(*) OVER(PARTITION BY X) [Records]
            FROM    T
        )
ORDER BY Records DESC

最后，我从来没有使用过 oracle 所以 apolgies 没有添加一个适用于 oracle 的解决方案......

编辑

我的 MySQL 解决方案没有考虑关系，我的建议是针对您所说要避免的内容(重复子查询)提出的此类步骤的解决方案，所以我不确定我是否能提供帮助，但是以防万一，这里有一个可以根据您的 fiddle 要求工作的版本:

SELECT  T.*
FROM    T
        INNER JOIN
        (   SELECT  X
            FROM    T
            GROUP BY X
            HAVING  COUNT(*) = 
                    (   SELECT  COUNT(*) AS Records
                        FROM    T
                        GROUP BY X
                        ORDER BY Records DESC
                        LIMIT 1
                    )
        ) T2
            ON T2.X = T.X