Oracle SQL多行合并为单行：查询部分场景失效问题求助

需求 #

将关联行合并为单行。

示例表 #

预期结果 #

将相关行合并为单行，效果如下：

问题说明 #

编写的Oracle SQL查询仅在19这类场景下正常工作，01.11这类带前导零的场景无法正确合并行，需排查原因。原查询代码如下：

WITH                            
COLUMN_8 AS (SELECT COL_F AS COLUMN_8, COL_B AS COLUMN_7, COL_C AS COLUMN_22 FROM TABLE WHERE COL_F LIKE '%.__%'),                          
COLUMN_6 AS (SELECT COL_E AS COLUMN_6, COL_B AS COLUMN_5 FROM TABLE WHERE COL_E LIKE '%._%'),                           
COLUMN_4 AS (SELECT COL_D AS COLUMN_4, COL_B AS COLUMN_3, COL_C AS COLUMN_21 FROM TABLE),                           
COLUMN_2 AS (SELECT COL_A AS COLUMN_2, COL_B AS COLUMN_1 FROM TABLE)                            
SELECT "COLUMN_8", "COLUMN_7", "COLUMN_6", "COLUMN_5", "COLUMN_4", "COLUMN_3", "COLUMN_2", "COLUMN_1"                           
FROM COLUMN_8 c                          
LEFT JOIN COLUMN_6 g ON TO_CHAR(TRUNC(c.COLUMN_8,1)) LIKE TO_CHAR(g.COLUMN_6)                           
LEFT JOIN COLUMN_4 d ON TRUNC(g.COLUMN_6,0) LIKE TO_CHAR(d.COLUMN_4)                            
LEFT JOIN COLUMN_2 s ON d.COLUMN_21 = s.COLUMN_2 OR s.COLUMN_2 = c.COLUMN_22;                           

核心问题原因 #

1. 数值转字符串格式不统一
   处理01.11时，TRUNC(c.COLUMN_8,1)得到01.1，但默认TO_CHAR()转换会去掉前导零，变成1.1；若COLUMN_6中存储的是带前导零的01.1，两者无法匹配，导致关联失败。而19这类无前列零的数值，转换后格式一致，所以能正常关联。

2. TRUNC与LIKE匹配逻辑冗余
   当前LIKE未使用通配符，本质是精确匹配，用=更合适，但核心问题还是格式不一致导致的匹配失败。

3. OR关联条件存在歧义
   最后一个JOIN的OR条件，当前面的关联返回NULL时（比如d.COLUMN_21为NULL），可能错误匹配到无关行，进一步导致结果异常。

修正方案 #

1. 统一字符串转换格式

使用TO_CHAR的格式化参数，强制保留前导零和指定小数位数，确保转换后字符串完全一致：

-- 保留两位整数、一位小数，去掉多余空格
TO_CHAR(TRUNC(c.COLUMN_8,1), 'FM00.0') = TO_CHAR(g.COLUMN_6, 'FM00.0')

可根据实际数值的位数调整格式模板（比如FM00用于整数部分）。

2. 替换LIKE为精确匹配

无通配符时用=代替LIKE，减少不必要的匹配逻辑，提升性能。

3. 优化OR关联条件

增加非空判断，避免NULL值导致的错误匹配：

LEFT JOIN COLUMN_2 s ON 
    (d.COLUMN_21 IS NOT NULL AND d.COLUMN_21 = s.COLUMN_2) 
    OR (c.COLUMN_22 IS NOT NULL AND s.COLUMN_2 = c.COLUMN_22)

完整修正后的SQL

WITH                            
COLUMN_8 AS (SELECT COL_F AS COLUMN_8, COL_B AS COLUMN_7, COL_C AS COLUMN_22 FROM TABLE WHERE COL_F LIKE '%.__%'),                          
COLUMN_6 AS (SELECT COL_E AS COLUMN_6, COL_B AS COLUMN_5 FROM TABLE WHERE COL_E LIKE '%._%'),                           
COLUMN_4 AS (SELECT COL_D AS COLUMN_4, COL_B AS COLUMN_3, COL_C AS COLUMN_21 FROM TABLE),                           
COLUMN_2 AS (SELECT COL_A AS COLUMN_2, COL_B AS COLUMN_1 FROM TABLE)                            
SELECT "COLUMN_8", "COLUMN_7", "COLUMN_6", "COLUMN_5", "COLUMN_4", "COLUMN_3", "COLUMN_2", "COLUMN_1"                           
FROM COLUMN_8 c                          
LEFT JOIN COLUMN_6 g ON TO_CHAR(TRUNC(c.COLUMN_8,1), 'FM00.0') = TO_CHAR(g.COLUMN_6, 'FM00.0')                           
LEFT JOIN COLUMN_4 d ON TO_CHAR(TRUNC(g.COLUMN_6,0), 'FM00') = TO_CHAR(d.COLUMN_4, 'FM00')                            
LEFT JOIN COLUMN_2 s ON 
    (d.COLUMN_21 IS NOT NULL AND d.COLUMN_21 = s.COLUMN_2) 
    OR (c.COLUMN_22 IS NOT NULL AND s.COLUMN_2 = c.COLUMN_22);

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