SQL Count()返回全1值问题排查

我一直在找这个问题的解决方案，但没找到完全匹配的案例。源数据来自Microsoft Dynamics AX（以下内容经过调整，但真实反映我的场景）。

查询基于SQL Server 2008，通过SSMS执行，返回的表中ITEMID列存在重复，但ITEMID/SECTIONID组合唯一。每个ITEMID可对应多个SECTIONID，我想要统计每个ITEMID对应的SECTIONID总数，然后把这个总数显示在每个ITEMID的每一行记录里。

但添加统计逻辑后，所有项的COUNT值都返回1，不是预期的2或更大数值。

我的完整查询 #

WITH cteSections AS (

   SELECT 

        i.ITEMID,
        r.SECTIONID,
        RN = ROW_NUMBER()OVER(PARTITION BY i.ITEMID, r.SECTIONID ORDER BY i.ITEMID)

    FROM InventTable i

    LEFT JOIN RetailInventItemSectionLocation r ON i.ITEMID = r.ITEMID

    LEFT JOIN InventSum s ON i.ITEMID = s.ITEMID

    WHERE s.AVAILPHYSICAL <> 0 AND r.STOREID = '00001'

)

SELECT 

    cteSections.ITEMID,
    cteSections.SECTIONID,
    COUNT(*) AS COUNT

FROM cteSections

WHERE cteSections.RN = 1

GROUP BY cteSections.ITEMID,
    cteSections.SECTIONID

ORDER BY cteSections.ITEMID

预期输出 #

但无论怎么调整，COUNT列始终全为1，请问我哪里出错了？

问题原因 #

你当前的逻辑有两个核心问题：

1. ROW_NUMBER分区错误：你用PARTITION BY i.ITEMID, r.SECTIONID，这会让每个ITEMID+SECTIONID组合只生成RN=1的记录，后续WHERE RN=1过滤后，每个分组里只有1条数据，COUNT(*)自然是1。
2. 分组逻辑不符合需求：你按ITEMID+SECTIONID分组，统计的是每个组合的行数，但你想要的是每个ITEMID对应的SECTIONID总数，然后把这个总数带到该ITEMID的每一行。

解决方案 #

不需要用ROW_NUMBER去重，直接用窗口函数COUNT() OVER (PARTITION BY ITEMID)来统计每个ITEMID的SECTIONID数量：

WITH cteSections AS (
    SELECT DISTINCT
        i.ITEMID,
        r.SECTIONID
    FROM InventTable i
    LEFT JOIN RetailInventItemSectionLocation r ON i.ITEMID = r.ITEMID
    LEFT JOIN InventSum s ON i.ITEMID = s.ITEMID
    WHERE s.AVAILPHYSICAL <> 0 AND r.STOREID = '00001'
)
SELECT
    ITEMID,
    SECTIONID,
    COUNT(*) OVER (PARTITION BY ITEMID) AS COUNT
FROM cteSections
ORDER BY ITEMID;

说明 #

1. 先用DISTINCT确保ITEMID+SECTIONID组合唯一（因为你的源数据里这个组合是唯一的，也可以省略，但加上更保险）。
2. 使用COUNT(*) OVER (PARTITION BY ITEMID)窗口函数，为每个ITEMID计算对应的SECTIONID总数，并把这个值填充到该ITEMID的每一行记录中，正好符合你的预期输出。

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