如何实现三个CTE按cte_1.InvoiceDate11=cte_2.InvoiceDate11=cte_3.EndDate11关联查询

原代码存在的问题 #

• 多CTE语法错误：多个CTE共用一个WITH关键字，之间用逗号分隔，不需要重复写WITH
• GROUP BY字段不匹配：cte_1查询选了InvoiceDate11但分组用InvoiceDate1，cte_2查询选了InvoiceDate11但分组用InvoiceDate2，会直接执行报错
• 别名语法错误：as_number_of_Customers中间缺空格，用#作为别名开头的字符需要加引号包裹避免语法报错
• CTE内部逻辑缺失：你当前三个CTE的AS后面括号是空的，需要先补全对应CTE的内部查询逻辑再执行后续关联

正确实现代码 #

WITH 
-- 补全cte_1内部逻辑后保留原有统计逻辑
cte_1 AS (
    -- 此处填写你原本cte_1的内部查询逻辑
),
cte_1_stat AS (
    SELECT 
        InvoiceDate11,
        COUNT(ContractId) AS number_of_Customers
    FROM cte_1 
    WHERE ROW_NUM = 1 AND InvoiceDate11 > '2019-01'
    GROUP BY InvoiceDate11
),
-- 补全cte_2内部逻辑后保留原有统计逻辑
cte_2 AS (
    -- 此处填写你原本cte_2的内部查询逻辑
),
cte_2_stat AS (
    SELECT 
        InvoiceDate11, 
        COUNT(InvoiceDate11) AS New_customers 
    FROM cte_2
    WHERE ROW_NUM = 1 AND FirstInvoiceDate1 = 1 
    GROUP BY InvoiceDate11
),
-- 补全cte_3内部逻辑后保留原有统计逻辑
cte_3 AS (
    -- 此处填写你原本cte_3的内部查询逻辑
),
cte_3_stat AS (
    SELECT 
        EndDate11,
        COUNT(EndDate11) AS `#ofcancellation`
    FROM cte_3
    WHERE ROW_NUM = 1   
    AND EndDate11 IS NOT NULL 
    GROUP BY EndDate11
)
-- 关联三个统计结果，此处用全外连接保留所有存在的日期，可根据需求换为INNER JOIN
SELECT 
    COALESCE(c1.InvoiceDate11, c2.InvoiceDate11, c3.EndDate11) AS stat_date,
    COALESCE(c1.number_of_Customers, 0) AS number_of_Customers,
    COALESCE(c2.New_customers, 0) AS New_customers,
    COALESCE(c3.`#ofcancellation`, 0) AS cancellation_count
FROM cte_1_stat c1
FULL OUTER JOIN cte_2_stat c2 ON c1.InvoiceDate11 = c2.InvoiceDate11
FULL OUTER JOIN cte_3_stat c3 ON COALESCE(c1.InvoiceDate11, c2.InvoiceDate11) = c3.EndDate11
ORDER BY stat_date;

关联逻辑说明 #

• 如果只需要三个CTE都存在对应日期的记录，把上面的FULL OUTER JOIN换成INNER JOIN即可
• COALESCE函数用于处理关联时的空值，没有对应统计值的日期默认补0，可根据业务需求调整
• 统计值别名避免使用特殊字符开头，如果一定要用需要加反引号包裹避免语法错误

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