不用EXCEPT实现Base表与Exclude表按YEAR匹配且排除指定BRAND

我们需要从Base表（示例中为DATA CTE）中筛选出不在Exclude表同年度BRAND列表的记录，最终保留ID为3、4、6的数据。除了EXCEPT，有多种方法可以实现，包括你提到的APPLY，下面是具体方案：

方案1：使用NOT EXISTS（最常用且高效） #

语义最直观的写法，直接判断当前DATA记录的YEAR和BRAND不存在于EXCLUDE表中：

WITH EXCLUDE AS
(
SELECT 2023 AS YEAR, 'A' AS BRAND
UNION ALL
SELECT 2023 AS YEAR, 'B' AS BRAND
UNION ALL
SELECT 2024 AS YEAR, 'C' AS BRAND
UNION ALL
SELECT 2024 AS YEAR, 'D' AS BRAND
)
, DATA AS
(
select 1 as ID, 2023 AS YEAR, 'A' AS BRAND
UNION ALL
SELECT 2 AS ID, 2023 AS YEAR, 'B' AS BRAND
UNION ALL
SELECT 3 AS ID, 2023 AS YEAR, 'C' AS BRAND
UNION ALL
SELECT 4 AS ID, 2023 AS YEAR, 'D' AS BRAND
UNION ALL
SELECT 5 AS ID, 2024 AS YEAR, 'D' AS BRAND
UNION ALL
SELECT 6 AS ID, 2024 AS YEAR, 'F' AS BRAND
)                   
SELECT D.* 
FROM DATA D
WHERE NOT EXISTS (
    SELECT 1 
    FROM EXCLUDE E 
    WHERE D.YEAR = E.YEAR 
      AND D.BRAND = E.BRAND
)

方案2：使用LEFT JOIN + IS NULL #

通过左关联EXCLUDE表，筛选关联不上的记录：

WITH EXCLUDE AS
(
SELECT 2023 AS YEAR, 'A' AS BRAND
UNION ALL
SELECT 2023 AS YEAR, 'B' AS BRAND
UNION ALL
SELECT 2024 AS YEAR, 'C' AS BRAND
UNION ALL
SELECT 2024 AS YEAR, 'D' AS BRAND
)
, DATA AS
(
select 1 as ID, 2023 AS YEAR, 'A' AS BRAND
UNION ALL
SELECT 2 AS ID, 2023 AS YEAR, 'B' AS BRAND
UNION ALL
SELECT 3 AS ID, 2023 AS YEAR, 'C' AS BRAND
UNION ALL
SELECT 4 AS ID, 2023 AS YEAR, 'D' AS BRAND
UNION ALL
SELECT 5 AS ID, 2024 AS YEAR, 'D' AS BRAND
UNION ALL
SELECT 6 AS ID, 2024 AS YEAR, 'F' AS BRAND
)                   
SELECT D.* 
FROM DATA D
LEFT JOIN EXCLUDE E 
    ON D.YEAR = E.YEAR 
    AND D.BRAND = E.BRAND
WHERE E.YEAR IS NULL

方案3：使用APPLY（符合你的需求） #

通过OUTER APPLY关联后筛选无匹配的记录：

WITH EXCLUDE AS
(
SELECT 2023 AS YEAR, 'A' AS BRAND
UNION ALL
SELECT 2023 AS YEAR, 'B' AS BRAND
UNION ALL
SELECT 2024 AS YEAR, 'C' AS BRAND
UNION ALL
SELECT 2024 AS YEAR, 'D' AS BRAND
)
, DATA AS
(
select 1 as ID, 2023 AS YEAR, 'A' AS BRAND
UNION ALL
SELECT 2 AS ID, 2023 AS YEAR, 'B' AS BRAND
UNION ALL
SELECT 3 AS ID, 2023 AS YEAR, 'C' AS BRAND
UNION ALL
SELECT 4 AS ID, 2023 AS YEAR, 'D' AS BRAND
UNION ALL
SELECT 5 AS ID, 2024 AS YEAR, 'D' AS BRAND
UNION ALL
SELECT 6 AS ID, 2024 AS YEAR, 'F' AS BRAND
)                   
SELECT D.* 
FROM DATA D
OUTER APPLY (
    SELECT 1 
    FROM EXCLUDE E 
    WHERE D.YEAR = E.YEAR 
      AND D.BRAND = E.BRAND
) AS E
WHERE E.1 IS NULL

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