如何基于相同FINENTRYGXID填充GXNAME列的NULL值

原SQL查询 #

SELECT 
    GXFINENTRYLINES.GXID, GXFINENTRY.GXID FINENTRYGXID, 
    GXTRADER.GXNAME, GXFINENTRYLINES.GXLINENUM AS LINENUM,  
    GXFINENTRYLINES.GXTRNVALUE AS GXTRNVAL  
FROM 
    GXFINENTRYLINES  
LEFT JOIN 
    GXTRADEENTRY ON GXFINENTRYLINES.GXTENTID = GXTRADEENTRY.GXID  
LEFT JOIN 
    GXFINENTRY ON GXFINENTRYLINES.GXFENTID = GXFINENTRY.GXID  
INNER JOIN 
    GXFINENTRYTYPE ON GXFINENTRY.GXFETPID = GXFINENTRYTYPE.GXID  
LEFT JOIN 
    GXTRADER ON GXFINENTRYLINES.GXTRDRID = GXTRADER.GXID

原查询结果 #

期望结果 #

解决方案 #

方法1：使用窗口函数（推荐，支持PostgreSQL、MySQL 8+、SQL Server等） #

利用MAX()窗口函数按FINENTRYGXID分组，提取该分组内LINENUM=-1的GXNAME值，替换NULL：

SELECT 
    t.GXID,
    t.FINENTRYGXID,
    COALESCE(t.GXNAME, MAX(CASE WHEN t.LINENUM = -1 THEN t.GXNAME END) OVER (PARTITION BY t.FINENTRYGXID)) AS GXNAME,
    t.LINENUM,
    t.GXTRNVAL
FROM (
    -- 原查询作为子查询
    SELECT 
        GXFINENTRYLINES.GXID, GXFINENTRY.GXID FINENTRYGXID, 
        GXTRADER.GXNAME, GXFINENTRYLINES.GXLINENUM AS LINENUM,  
        GXFINENTRYLINES.GXTRNVALUE AS GXTRNVAL  
    FROM 
        GXFINENTRYLINES  
    LEFT JOIN 
        GXTRADEENTRY ON GXFINENTRYLINES.GXTENTID = GXTRADEENTRY.GXID  
    LEFT JOIN 
        GXFINENTRY ON GXFINENTRYLINES.GXFENTID = GXFINENTRY.GXID  
    INNER JOIN 
        GXFINENTRYTYPE ON GXFINENTRY.GXFETPID = GXFINENTRYTYPE.GXID  
    LEFT JOIN 
        GXTRADER ON GXFINENTRYLINES.GXTRDRID = GXTRADER.GXID
) t

方法2：子查询关联（兼容旧版数据库） #

先查询每个FINENTRYGXID对应的LINENUM=-1的GXNAME，再关联原查询结果替换NULL：

SELECT 
    o.GXID,
    o.FINENTRYGXID,
    COALESCE(o.GXNAME, g.GROUP_GXNAME) AS GXNAME,
    o.LINENUM,
    o.GXTRNVAL
FROM (
    -- 原查询结果
    SELECT 
        GXFINENTRYLINES.GXID, GXFINENTRY.GXID FINENTRYGXID, 
        GXTRADER.GXNAME, GXFINENTRYLINES.GXLINENUM AS LINENUM,  
        GXFINENTRYLINES.GXTRNVALUE AS GXTRNVAL  
    FROM 
        GXFINENTRYLINES  
    LEFT JOIN 
        GXTRADEENTRY ON GXFINENTRYLINES.GXTENTID = GXTRADEENTRY.GXID  
    LEFT JOIN 
        GXFINENTRY ON GXFINENTRYLINES.GXFENTID = GXFINENTRY.GXID  
    INNER JOIN 
        GXFINENTRYTYPE ON GXFINENTRY.GXFETPID = GXFINENTRYTYPE.GXID  
    LEFT JOIN 
        GXTRADER ON GXFINENTRYLINES.GXTRDRID = GXTRADER.GXID
) o
LEFT JOIN (
    -- 获取每个分组的基准GXNAME
    SELECT 
        GXFINENTRY.GXID FINENTRYGXID,
        GXTRADER.GXNAME GROUP_GXNAME
    FROM GXFINENTRYLINES
    LEFT JOIN GXFINENTRY ON GXFINENTRYLINES.GXFENTID = GXFINENTRY.GXID
    LEFT JOIN GXTRADER ON GXFINENTRYLINES.GXTRDRID = GXTRADER.GXID
    WHERE GXFINENTRYLINES.GXLINENUM = -1
) g ON o.FINENTRYGXID = g.FINENTRYGXID

说明 #

• COALESCE函数用于判断当前行的GXNAME是否为NULL，若是则替换为分组内的基准名称；
• 窗口函数方法无需额外关联，性能更优，适合支持窗口函数的数据库；
• 子查询方法兼容性更强，适合不支持窗口函数的旧版数据库。

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