如何用SQL合并TPTTYP为'AU'和'U'的行并填充TPMDE8字段

需求说明 #

当TPTTYP='AU'时，若TPMDE8字段为空，需用**同组（JL、FROM、CEL、BUFOR、ART字段值一致）**中TPTTYP='U'的TPMDE8值进行替换；最终仅保留TPTTYP='AU'且TPMDE8字段有值的行。

当前数据状态 #

期望结果 #

现有查询语句 #

select tpttyp, TPLENR as JL, (tpvber|| ' ' || tpvreg|| '-' ||tpvhor|| '-' ||tpvver) as Z,
(tpnber|| ' ' || tpnreg|| '-' ||tpnhor|| '-' ||tpnver) as na,
TPPPLZ as bufor,
trim(tpiden) as art, 
(to_date(right('00' || TPTSTA,2) || '-' || right('00' || TPTSMO,2) || '-' || TPTSJH || right('00' || TPTSJA,2) || right('00' || tptsst,2) || ':' || right('00' ||tptsmi ,2) || ':' || right('00'|| tptsse ,2),'DD/MM/YYYY HH24:MI:SS')) as Podjete,
tpmde8

from xyz
where
...

实现方案 #

完全可以通过SQL实现该需求，以下提供两种常见的实现方式：

方式一：使用窗口函数（推荐，适配多数数据库） #

基于现有查询，通过窗口函数提取同组内TPTTYP='U'的TPMDE8值，替换AU行的空值：

WITH base_data AS (
    select 
        tpttyp, 
        TPLENR as JL, 
        (tpvber|| ' ' || tpvreg|| '-' ||tpvhor|| '-' ||tpvver) as "FROM",
        (tpnber|| ' ' || tpnreg|| '-' ||tpnhor|| '-' ||tpnver) as CEL,
        TPPPLZ as bufor,
        trim(tpiden) as art, 
        (to_date(right('00' || TPTSTA,2) || '-' || right('00' || TPTSMO,2) || '-' || TPTSJH || right('00' || TPTSJA,2) || right('00' || tptsst,2) || ':' || right('00' ||tptsmi ,2) || ':' || right('00'|| tptsse ,2),'DD/MM/YYYY HH24:MI:SS')) as Podjete,
        tpmde8
    from xyz
    where ... -- 保留原查询的WHERE条件
)
SELECT 
    tpttyp,
    JL,
    "FROM",
    CEL,
    bufor,
    art,
    Podjete,
    COALESCE(tpmde8, MAX(CASE WHEN tpttyp = 'U' THEN tpmde8 END) OVER (PARTITION BY JL, "FROM", CEL, bufor, art)) AS TPMDE8
FROM base_data
WHERE tpttyp = 'AU'
HAVING COALESCE(tpmde8, MAX(CASE WHEN tpttyp = 'U' THEN tpmde8 END) OVER (PARTITION BY JL, "FROM", CEL, bufor, art)) IS NOT NULL;

方式二：使用自连接 #

通过自连接匹配同组的U行，填充AU行的空值：

SELECT 
    a.tpttyp,
    a.JL,
    a."FROM",
    a.CEL,
    a.bufor,
    a.art,
    a.Podjete,
    COALESCE(a.tpmde8, b.tpmde8) AS TPMDE8
FROM (
    select 
        tpttyp, 
        TPLENR as JL, 
        (tpvber|| ' ' || tpvreg|| '-' ||tpvhor|| '-' ||tpvver) as "FROM",
        (tpnber|| ' ' || tpnreg|| '-' ||tpnhor|| '-' ||tpnver) as CEL,
        TPPPLZ as bufor,
        trim(tpiden) as art, 
        (to_date(right('00' || TPTSTA,2) || '-' || right('00' || TPTSMO,2) || '-' || TPTSJH || right('00' || TPTSJA,2) || right('00' || tptsst,2) || ':' || right('00' ||tptsmi ,2) || ':' || right('00'|| tptsse ,2),'DD/MM/YYYY HH24:MI:SS')) as Podjete,
        tpmde8
    from xyz
    where tpttyp = 'AU'
        and ... -- 保留原查询的WHERE条件
) a
LEFT JOIN (
    select 
        TPLENR as JL, 
        (tpvber|| ' ' || tpvreg|| '-' ||tpvhor|| '-' ||tpvver) as "FROM",
        (tpnber|| ' ' || tpnreg|| '-' ||tpnhor|| '-' ||tpnver) as CEL,
        TPPPLZ as bufor,
        trim(tpiden) as art, 
        tpmde8
    from xyz
    where tpttyp = 'U'
        and ... -- 保留原查询的WHERE条件
) b ON a.JL = b.JL 
    AND a."FROM" = b."FROM" 
    AND a.CEL = b.CEL 
    AND a.bufor = b.bufor 
    AND a.art = b.art
WHERE COALESCE(a.tpmde8, b.tpmde8) IS NOT NULL;

注：根据使用的数据库类型，可能需要调整部分函数语法（比如RIGHT()在部分数据库中需替换为SUBSTRING()），但核心逻辑通用。

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