如何在复杂SQL查询中无需UNION合并表生成含Applied事件的视图

我明白你的痛点——原查询已经是5张表关联+100多行代码，用UNION的话得把复杂逻辑写两遍，太冗余了。下面给你一个用横向关联实现的方案，完美避开UNION，还能生成你要的目标视图。

先明确源表结构 #

表A现有数据 #

id  event        timestamp               registered  acct_add  funded  applied
1   Register     5/18/2018 4:00:00 AM    1           0         0       0
1   AddAccount   5/18/2018 5:00:00 AM    0           1         0       0
1   Funded       5/18/2018 6:00:00 AM    0           0         1       0
2   Register     5/18/2018 7:00:00 AM    1           0         0       0
2   AddAccount   5/18/2018 8:00:00 AM    0           1         0       0
2   Funded       5/18/2018 9:00:00 AM    0           0         1       0

表B转换后的数据 #

id  application_date         applied
1   5/18/2018 6:30:00 AM    1
2   5/18/2018 9:30:00 AM    1

核心解决方案：用横向关联扩展行 #

这个思路是把每个id的原有记录和对应的Applied事件记录通过横向关联拼接成一组行，不用重复写原查询的复杂逻辑。

适用于PostgreSQL/MySQL 8.0+（用LATERAL JOIN） #

WITH original_data AS (
    -- 把你那100多行的复杂查询放这里，生成和表A结构一致的结果
    SELECT id, event, timestamp, registered, acct_add, funded, applied
    FROM your_complex_table_joins
)
SELECT 
    COALESCE(od.id, b.id) AS id,
    COALESCE(od.event, 'Applied') AS event,
    COALESCE(od.timestamp, b.application_date) AS timestamp,
    COALESCE(od.registered, 0) AS registered,
    COALESCE(od.acct_add, 0) AS acct_add,
    COALESCE(od.funded, 0) AS funded,
    COALESCE(od.applied, b.applied) AS applied
FROM original_data od
FULL JOIN LATERAL (
    SELECT id, application_date, applied
    FROM table_b
    WHERE table_b.id = od.id
) b ON TRUE
ORDER BY id, timestamp;

适用于SQL Server（用CROSS APPLY） #

WITH original_data AS (
    -- 你的复杂查询，生成表A结构的结果
    SELECT id, event, timestamp, registered, acct_add, funded, applied
    FROM your_complex_table_joins
)
SELECT 
    x.id,
    x.event,
    x.timestamp,
    x.registered,
    x.acct_add,
    x.funded,
    x.applied
FROM original_data od
LEFT JOIN table_b b ON od.id = b.id
CROSS APPLY (
    -- 第一行：原表A的记录
    VALUES 
        (od.id, od.event, od.timestamp, od.registered, od.acct_add, od.funded, od.applied),
        -- 第二行：表B的Applied事件记录
        (b.id, 'Applied', b.application_date, 0, 0, 0, b.applied)
) x(id, event, timestamp, registered, acct_add, funded, applied)
WHERE x.id IS NOT NULL -- 过滤掉无Applied记录的id的空行
ORDER BY x.id, x.timestamp;

方案优势 #

1. 避免代码冗余：原查询只需要写一次放在CTE里，不用像UNION那样重复整个复杂逻辑
2. 灵活处理边界情况：如果某个id没有触发Applied事件，用LEFT JOIN配合COALESCE就能自动过滤或填充默认值
3. 性能友好：横向关联的执行计划通常比UNION更高效，尤其是原查询涉及大量计算或关联的时候

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