SQL实现统计关联YNumber最多的XNumber查询方法

你写的查询返回所有计数为1的核心原因是分组逻辑错误：

• 你同时按XNumber和YNumber两个字段分组，每个分组对应唯一的一组X-Y关联配对，自然每组计数都是1
• 要统计每个XNumber关联的YNumber总数，仅需要按XNumber单字段分组即可

以下写法兼容你当前使用的SQL Server环境，支持并列第一的场景（如果多个XNumber关联的Y数量同为最高，会全部返回）：

WITH XRelationCount AS (
    SELECT
        X.XNumber,
        COUNT(DISTINCT Y.YNumber) AS RelatedYTotal
    FROM XY
    JOIN X ON X.XID = XY.XID
    JOIN Y ON Y.YID = XY.YID
    GROUP BY X.XNumber
)
SELECT XNumber, RelatedYTotal
FROM (
    SELECT
        XNumber,
        RelatedYTotal,
        RANK() OVER(ORDER BY RelatedYTotal DESC) AS SortRank
    FROM XRelationCount
) res
WHERE SortRank = 1;

语句说明：

• 用COUNT(DISTINCT Y.YNumber)是为了避免XY表存在重复XID-YID映射时计数虚高，如果你给XY表加了XID+YID的联合唯一约束，可以去掉DISTINCT提升性能
• 用RANK()而非ROW_NUMBER()是为了兼容并列第一的场景，不会漏掉关联数相同的XNumber

如果你使用的数据库版本不支持窗口函数，可以用以下兼容写法：

SELECT
    X.XNumber,
    COUNT(DISTINCT Y.YNumber) AS RelatedYTotal
FROM XY
JOIN X ON X.XID = XY.XID
JOIN Y ON Y.YID = XY.YID
GROUP BY X.XNumber
HAVING COUNT(DISTINCT Y.YNumber) = (
    SELECT MAX(RelatedCount)
    FROM (
        SELECT COUNT(DISTINCT Y.YNumber) AS RelatedCount
        FROM XY
        JOIN Y ON Y.YID = XY.YID
        GROUP BY XY.XID
    ) countRes
);

根据你插入的测试数据，关联YNumber最多的XNumber如下：

• XNumber：GKHL
• 关联YNumber总数：3

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