如何简化含多个DISTINCT子句的SQL Server客户访问查询

完全匹配需求的简化查询 #

核心思路是用窗口函数一次性计算所需统计指标，避免多次分组和去重操作，提升查询效率：

SELECT DISTINCT v.CustomerID
FROM (
    SELECT 
        CustomerID,
        StoreID,
        -- 计算客户访问过的不同商店总数
        COUNT(DISTINCT StoreID) OVER (PARTITION BY CustomerID) AS TotalUniqueStores,
        -- 标记客户是否访问过指定集合外的商店
        MAX(CASE WHEN StoreID NOT IN (1,10,16,42) THEN 1 ELSE 0 END) OVER (PARTITION BY CustomerID) AS HasInvalidStore
    FROM Visit
) v
WHERE v.TotalUniqueStores = 1 
  AND v.HasInvalidStore = 0
  AND v.StoreID IN (1,10,16,42)

逻辑说明

• 内层子查询仅扫描一次Visit表，通过窗口函数计算两个关键值：
  1. TotalUniqueStores：每个客户访问过的不同商店数量
  2. HasInvalidStore：若客户访问过指定集合外的商店则标记为1，否则为0
• 外层筛选条件直接过滤出：仅访问1家商店、未访问过违规商店、且该商店在指定集合内的客户
• 外层的DISTINCT用于去除同一客户多次访问同一家店产生的重复行

仅排除访问过指定集合外商店的简化查询 #

如果可以接受不限制访问指定集合内商店的数量，查询可以更简洁：

方案1：子查询排除法

SELECT DISTINCT CustomerID
FROM Visit
WHERE CustomerID NOT IN (
    SELECT CustomerID 
    FROM Visit 
    WHERE StoreID NOT IN (1,10,16,42)
)

方案2：窗口函数法

SELECT DISTINCT CustomerID
FROM (
    SELECT 
        CustomerID,
        MAX(CASE WHEN StoreID NOT IN (1,10,16,42) THEN 1 ELSE 0 END) OVER (PARTITION BY CustomerID) AS HasInvalidStore
    FROM Visit
) v
WHERE v.HasInvalidStore = 0

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