如何修改SQL查询，仅输出location实例数>3的Item相关行

你需要筛选出对应Item拥有超过3个不同location实例的分组结果，直接在原查询的HAVING子句中添加条件会失效——因为原查询是按location和Item分组，每个分组里的location是唯一的，COUNT(DISTINCT location)永远等于1，无法满足大于3的条件。

下面提供两种可行的修改方案：

方案一：子查询预筛选符合条件的Item #

先通过子查询找出满足条件（拥有超过3个不同location）的Item，再关联原查询获取分组求和结果：

SELECT t.location, t.Item, SUM(t.TxQty) AS Total
FROM tblimInvTxHistory t
INNER JOIN (
    -- 先筛选出location实例数>3的Item
    SELECT Item
    FROM tblimInvTxHistory
    WHERE TxDate > '2022-01-01' AND TxCode IN ('COSHIP', 'SHIP')
    GROUP BY Item
    HAVING COUNT(DISTINCT location) > 3
) filtered_items ON t.Item = filtered_items.Item
WHERE t.TxDate > '2022-01-01' AND t.TxCode IN ('COSHIP', 'SHIP')
GROUP BY t.location, t.Item
ORDER BY Item

方案二：窗口函数计算location实例数 #

使用窗口函数在分组时直接计算每个Item对应的location总数，再过滤结果：

WITH grouped_data AS (
    SELECT 
        location, 
        Item, 
        SUM(TxQty) AS Total,
        -- 按Item分区，统计每个Item的不同location数量
        COUNT(DISTINCT location) OVER (PARTITION BY Item) AS location_count
    FROM tblimInvTxHistory
    WHERE TxDate > '2022-01-01' AND TxCode IN ('COSHIP', 'SHIP')
    GROUP BY location, Item
)
SELECT location, Item, Total
FROM grouped_data
WHERE location_count > 3
ORDER BY Item

两种方案都能实现需求，窗口函数的写法更简洁，子查询的方式在部分老版本SQL Server中兼容性更好。

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