如何借助CTE与Union查询同时满足双CTE条件的重复ID

我需要编写SQL查询，目标是找出同时满足两个CTE条件的ID，仅展示这些符合双条件的ID。不确定CTE中的ROW_NUMBER() OVER(PARTITION BY ID ORDER BY DATE)子句是否必要，但核心需求是标识出同时存在于两个CTE中的ID。

以下是基于表#complete_query_output编写的初始代码片段：

WITH CTE1 AS
        (SELECT *,
        ROW_NUMBER() OVER (PARTITION BY ID
        ORDER BY DATE) AS RN --row number
        FROM #complete_query_output
        WHERE Decision = 'Declined'
        AND xyz> 0 
        AND (Business_Rule_1= 1 OR Business_Rule_2= 1 OR Business_Rule_3= 1)
        ),
 
CTE2 AS
        (SELECT *,
        ROW_NUMBER() OVER (PARTITION BY ID
        ORDER BY DATE) AS RN
        FROM #complete_query_output
        WHERE abc = 1
        --AND Decision = 'Approved'
        )

SELECT *
FROM CTE1

UNION ALL

SELECT *
FROM CTE2

ORDER BY ID

我尝试过添加分组统计代码，但未得到预期结果，无法筛选出同时存在于两个CTE中的ID：

SELECT *, count(*)
FROM CTE1
GROUP BY ID
HAVING COUNT(*) > 1

UNION ALL

SELECT *, count(*)
FROM CTE2

GROUP BY ID
HAVING COUNT(*) > 1

解决方案 #

核心问题分析 #

• 初始代码用UNION ALL只是合并两个CTE的所有记录，没有筛选出同时存在于两个CTE的ID，不符合需求。
• 尝试的分组统计是找单个CTE内部重复出现的ID（同一CTE中ID出现多次），而不是找跨两个CTE都存在的ID，逻辑方向错误。

方案1：仅获取符合双条件的ID（最简版） #

如果只需要ID列表，不需要其他字段，直接用交集或IN/JOIN实现：

用IN子查询：

WITH CTE1 AS (
    SELECT DISTINCT ID
    FROM #complete_query_output
    WHERE Decision = 'Declined'
      AND xyz > 0 
      AND (Business_Rule_1 = 1 OR Business_Rule_2 = 1 OR Business_Rule_3 = 1)
),
CTE2 AS (
    SELECT DISTINCT ID
    FROM #complete_query_output
    WHERE abc = 1
      --AND Decision = 'Approved'
)
SELECT ID
FROM CTE1
WHERE ID IN (SELECT ID FROM CTE2)

用INNER JOIN：

WITH CTE1 AS (
    SELECT DISTINCT ID
    FROM #complete_query_output
    WHERE Decision = 'Declined'
      AND xyz > 0 
      AND (Business_Rule_1 = 1 OR Business_Rule_2 = 1 OR Business_Rule_3 = 1)
),
CTE2 AS (
    SELECT DISTINCT ID
    FROM #complete_query_output
    WHERE abc = 1
      --AND Decision = 'Approved'
)
SELECT c1.ID
FROM CTE1 c1
INNER JOIN CTE2 c2 ON c1.ID = c2.ID

方案2：保留原表字段，展示符合双条件ID的所有相关记录 #

如果需要展示这些ID对应的、符合任一CTE条件的所有记录：

WITH CTE1 AS (
    SELECT *
    FROM #complete_query_output
    WHERE Decision = 'Declined'
      AND xyz > 0 
      AND (Business_Rule_1 = 1 OR Business_Rule_2 = 1 OR Business_Rule_3 = 1)
),
CTE2 AS (
    SELECT *
    FROM #complete_query_output
    WHERE abc = 1
      --AND Decision = 'Approved'
),
-- 先筛选出同时存在于两个CTE的ID
CommonIDs AS (
    SELECT DISTINCT ID FROM CTE1
    INTERSECT
    SELECT DISTINCT ID FROM CTE2
)
-- 取出这些ID在两个CTE中的所有记录
SELECT * FROM CTE1 WHERE ID IN (SELECT ID FROM CommonIDs)
UNION ALL
SELECT * FROM CTE2 WHERE ID IN (SELECT ID FROM CommonIDs)
ORDER BY ID

关于ROW_NUMBER的使用 #

如果你的需求不仅是找ID，还要获取每个ID的最新/最早记录，才需要ROW_NUMBER()。比如要每个ID在CTE1和CTE2中的最新记录：

WITH CTE1 AS (
    SELECT *,
           ROW_NUMBER() OVER (PARTITION BY ID ORDER BY DATE DESC) AS RN
    FROM #complete_query_output
    WHERE Decision = 'Declined'
      AND xyz > 0 
      AND (Business_Rule_1 = 1 OR Business_Rule_2 = 1 OR Business_Rule_3 = 1)
),
CTE2 AS (
    SELECT *,
           ROW_NUMBER() OVER (PARTITION BY ID ORDER BY DATE DESC) AS RN
    FROM #complete_query_output
    WHERE abc = 1
      --AND Decision = 'Approved'
),
CTE1_Latest AS (
    SELECT * FROM CTE1 WHERE RN = 1
),
CTE2_Latest AS (
    SELECT * FROM CTE2 WHERE RN = 1
)
-- 找出同时有最新记录的ID，并取出对应记录
SELECT * FROM CTE1_Latest WHERE ID IN (SELECT ID FROM CTE2_Latest)
UNION ALL
SELECT * FROM CTE2_Latest WHERE ID IN (SELECT ID FROM CTE1_Latest)
ORDER BY ID

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