使用UNION ALL构造符合指定结构的SQL查询需求

原始查询语句 #

SELECT 'load' AS type,
       id,
       sec_id,
       start_date,
       end_date,
       max_date,
       NULL AS tble_id,
       NULL AS pTime
FROM tablea
UNION ALL
SELECT 'dump',
       NULL AS id,
       NULL AS sec_int,
       NULL AS start_date,
       NULL AS end_date,
       NULL AS max_date,
       id AS tbl_id,
       Ptime
FROM [table];

预期查询结果 #

核心要求 #

• load行的id、sec_id及日期字段信息需同步显示在对应dump行中
• dump行需区分展示进入和退出的pTime字段

已尝试的未完成CTE查询 #

WITH Combined AS (
    SELECT 
        'load' AS type, 
        id, 
        sec_id, 
        start_date, 
        end_date, 
        max_date, 
        NULL AS pTime 
    FROM tablea
    UNION ALL
    SELECT 
        'dump' AS type, 
        id, 
        sec_id AS sec_id,  -- 假设tableb中存在sec_id，可根据实际调整
        NULL AS start_date, 
        NULL AS end_date, 
        NULL AS max_date, 
        pTime 
    FROM tableb
)

完善后的SQL语句 #

假设tablea（load数据）与tableb（dump数据）通过id和sec_id关联，且tableb中存在action_type字段（值为enter/exit）区分进入、退出时间，以下是实现代码：

WITH LoadData AS (
    -- 提取tablea中load类型的核心数据
    SELECT 
        id, 
        sec_id, 
        start_date, 
        end_date, 
        max_date
    FROM tablea
),
DumpData AS (
    -- 聚合tableb的dump数据，拆分进入/退出时间
    SELECT 
        id,
        sec_id,
        MAX(CASE WHEN action_type = 'enter' THEN pTime END) AS pTime_enter,
        MAX(CASE WHEN action_type = 'exit' THEN pTime END) AS pTime_exit
    FROM tableb
    GROUP BY id, sec_id
)
-- 生成load行
SELECT 
    'load' AS type,
    ld.id,
    ld.sec_id,
    ld.start_date,
    ld.end_date,
    ld.max_date,
    NULL AS `pTime for enter`,
    NULL AS `pTime for exit`
FROM LoadData ld
UNION ALL
-- 生成关联load数据的dump行
SELECT 
    'dump' AS type,
    ld.id,
    ld.sec_id,
    ld.start_date,
    ld.end_date,
    ld.max_date,
    dd.pTime_enter,
    dd.pTime_exit
FROM LoadData ld
JOIN DumpData dd ON ld.id = dd.id AND ld.sec_id = dd.sec_id;

逻辑说明 #

1. LoadData CTE：单独提取tablea的核心字段，作为后续dump行的字段数据源
2. DumpData CTE：通过条件聚合将tableb中分散的进入、退出时间合并为两个独立字段（若tableb本身就有两个时间字段，直接选取即可，无需聚合）
3. 结果合并：
   • 第一部分直接输出load行，时间字段设为NULL
   • 第二部分通过关联LoadData和DumpData，将load的字段填充到dump行中，同时带入对应的进入/退出时间

若实际关联键或tableb的时间存储方式不同，只需修改JOIN条件或DumpData中的字段处理逻辑即可。

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