如何使用CTE联结多表并将联结结果存入新CTE？

• 需求：用CTE关联master表与transaction表，同时希望基于该关联结果再联结另一张表；另外想了解如何将两个查询的结果存入同一个CTE。
• 原SQL代码（存在语法错误）：

with CTE_base as (
select
account_number,
orgn_acct,
product_type,
load_date as report_date,
min(concat(case when 
substr(load(cast(acc_open_date as string),6,'0'),1,2)>'30' then '19'else '20' end,
substr(load(cast(acc_open_date as string),6,'0'),1,2),'-',
substr(load(cast(acc_open_date as string),6,'0'),3,2),'-',
substr(load(cast(acc_open_date as string),6,'0'),5,2)))) as open_date
from master
where 
product_type <400 
and product_type not between 290 and 390
and datediff(load_date,concat(case when 
substr(load(cast(acc_open_date as string),6,'0'),1,2)>'30' then '19'else '20' end,
substr(load(cast(acc_open_date as string),6,'0'),1,2),'-',
substr(load(cast(acc_open_date as string),6,'0'),3,2),'-',
substr(load(cast(acc_open_date as string),6,'0'),5,2))) <=398

group by load_date,orgn_acct,product_type,account_number)

select * from CTE_base)master

inner join(
with CTE_fees as (select
trans_code,
march_code,
account_num,
load_date,
case when (
(trans_code =253)
and (march_code =12)
then "annual fee")
end as fee_type)
from transaction)
select * from CTE_fees) fees

on fees.account_num =master.account_number
where datediff(fees.load_date,master.open_date )<=397

原SQL存在几个关键问题：

1. CTE定义逻辑错误：不能在JOIN子句里嵌套定义CTE_fees，所有CTE需在查询最开头统一定义，用逗号分隔多个CTE。
2. 语法遗漏：CASE语句缺少闭合括号，from transaction)多了一个冗余右括号。
3. 疑似笔误：load(cast(acc_open_date as string),6,'0')应为lpad（左补0函数），否则语法不成立。

1. 正确关联CTE并联结第三张表 #

先统一定义所有需要的CTE，再关联得到中间结果，最后用这个中间结果联结第三张表：

-- 统一定义所有CTE，逗号分隔
WITH CTE_base AS (
    SELECT
        account_number,
        orgn_acct,
        product_type,
        load_date AS report_date,
        -- 重构日期转换逻辑，避免重复代码
        MIN(
            CONCAT(
                CASE WHEN SUBSTR(LPAD(CAST(acc_open_date AS STRING), 6, '0'), 1, 2) > '30' THEN '19' ELSE '20' END,
                '-',
                SUBSTR(LPAD(CAST(acc_open_date AS STRING), 6, '0'), 1, 2),
                '-',
                SUBSTR(LPAD(CAST(acc_open_date AS STRING), 6, '0'), 3, 2),
                '-',
                SUBSTR(LPAD(CAST(acc_open_date AS STRING), 6, '0'), 5, 2)
            )
        ) AS open_date
    FROM master
    WHERE 
        product_type < 400 
        AND product_type NOT BETWEEN 290 AND 390
        AND DATEDIFF(
            load_date,
            CONCAT(
                CASE WHEN SUBSTR(LPAD(CAST(acc_open_date AS STRING), 6, '0'), 1, 2) > '30' THEN '19' ELSE '20' END,
                '-',
                SUBSTR(LPAD(CAST(acc_open_date AS STRING), 6, '0'), 1, 2),
                '-',
                SUBSTR(LPAD(CAST(acc_open_date AS STRING), 6, '0'), 3, 2),
                '-',
                SUBSTR(LPAD(CAST(acc_open_date AS STRING), 6, '0'), 5, 2)
            )
        ) <= 398
    GROUP BY load_date, orgn_acct, product_type, account_number
),
CTE_fees AS (
    SELECT
        trans_code,
        march_code,
        account_num,
        load_date,
        CASE 
            WHEN trans_code = 253 AND march_code = 12 THEN 'annual fee'
        END AS fee_type
    FROM transaction
),
-- 将前两个CTE的关联结果存入新CTE
CTE_joined AS (
    SELECT 
        mb.*,
        tf.trans_code,
        tf.march_code,
        tf.load_date AS fee_load_date,
        tf.fee_type
    FROM CTE_base mb
    INNER JOIN CTE_fees tf
        ON tf.account_num = mb.account_number
    WHERE DATEDIFF(tf.load_date, mb.open_date) <= 397
)
-- 用关联后的CTE联结第三张表（示例为customer表）
SELECT 
    cj.*,
    cu.customer_name,
    cu.contact_info
FROM CTE_joined cj
INNER JOIN customer cu
    ON cj.account_number = cu.account_num; -- 假设关联字段为account_number

2. 将两个查询结果存入同一个CTE的两种方式 #

方式一：用UNION ALL合并（需字段结构一致） #

如果两个查询的字段数量、类型匹配，可直接合并到同一个CTE：

WITH CTE_combined AS (
    -- 第一个查询：master表基础数据
    SELECT
        account_number,
        orgn_acct,
        product_type,
        report_date,
        open_date,
        'master_data' AS data_source
    FROM CTE_base
    UNION ALL
    -- 第二个查询：transaction表费用数据（调整字段匹配结构）
    SELECT
        account_num AS account_number,
        NULL AS orgn_acct,
        NULL AS product_type,
        load_date AS report_date,
        NULL AS open_date,
        'transaction_fee' AS data_source
    FROM CTE_fees
)
SELECT * FROM CTE_combined;

方式二：关联后存入CTE #

如上述示例中的CTE_joined，直接将两个CTE的关联结果定义为新的CTE，后续可直接复用该数据集。

• 所有CTE必须在WITH子句开头统一定义，多个CTE用逗号分隔，不能嵌套在JOIN或子查询里。
• 可以基于已定义的CTE再创建新的CTE，实现链式复用。
• 若要合并两个查询结果，需确保字段数量、类型一致，用UNION ALL（或UNION去重）。

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