如何从动态SQL结果集中提取两列去重值并合并为一列

我需要将两列的值合并为一列，且仅保留去重后的数据。我知道针对单表查询时可以用UNION实现，但如果数据是由包含100多列的动态SQL生成（如下示例），如果用常规方案就得重复写两次外层查询来做UNION并去重。想问下示例里用「带分隔符聚合唯一数据」的UNION组合方案是否可行？

动态SQL示例代码 #

SELECT DISTINCT ID, key_code, LOC_ID FROM
(
SELECT /*SOME 100 COLUMN*/, LOC_ID_1, LOC_ID_2 FROM TABLE 1
UNION ALL
SELECT /*SOME 100 COLUMN*/, LOC_ID_1, LOC_ID_2 FROM TABLE 2
UNION ALL
SELECT /*SOME 100 COLUMN*/, LOC_ID_1, LOC_ID_2 FROM TABLE 3
)

测试用例与验证 #

建表与插入数据 #

/*CREATE TABLE*/
CREATE TABLE get_data (
id       NUMBER,
key_code NUMBER,
loc_id_1   VARCHAR2(20),
loc_id_2   VARCHAR2(20)
);

/*INSERT DATA*/
INSERT INTO get_data (id,key_code,loc_id_1,loc_id_2) values (1,101,'522','545');
INSERT INTO get_data (id,key_code,loc_id_1,loc_id_2) values (1,101,'522','546');
INSERT INTO get_data (id,key_code,loc_id_1,loc_id_2) values (1,101,'523','547');
INSERT INTO get_data (id,key_code,loc_id_1,loc_id_2) values (1,101,'550','545');
INSERT INTO get_data (id,key_code,loc_id_1,loc_id_2) values (2,201,'721','344');
INSERT INTO get_data (id,key_code,loc_id_1,loc_id_2) values (2,201,'821','451');
INSERT INTO get_data (id,key_code,loc_id_1,loc_id_2) values (2,201,'821','653');

/*QUERY DATA*/
SELECT * FROM get_data ORDER BY 1;

原始数据输出 #

ID  KEY_CODE    LOC_ID_1    LOC_ID_2
1   101     522     545
1   101     522     546
1   101     523     547
1   101     550     545
2   201     721     344
2   201     821     451
2   201     821     653

测试查询语句 #

/*GETTING UNIQUE DATA WITH DELIMITER from both LOC_ID_1 AND LOC_ID_2*/
SELECT DISTINCT ID,key_code,
LISTAGG(loc_id,'||') WITHIN GROUP (ORDER BY loc_id) OVER (PARTITION BY ID) AS loc_id FROM (
SELECT ID,key_code,loc_id_1 AS loc_id FROM get_data
UNION
SELECT ID,key_code,loc_id_2 AS loc_id  FROM get_data
);

测试结果输出 #

ID KEY_CODE LOC_ID
1  101      522||523||545||546||547||550
2  201      344||451||653||721||821

结论与方案分析 #

结论：该方案完全可行 #

你的测试用例已经验证了这个方案的有效性，它确实能实现按ID分组，合并loc_id_1和loc_id_2的所有去重值并以分隔符拼接的需求。

核心逻辑拆解 #

• 纵向展开+去重：通过UNION将两列数据转为纵向结构，同时自动过滤重复值（UNION会隐式去重，UNION ALL则不会）
• 分组聚合拼接：用LISTAGG()窗口函数按ID分组，将去重后的loc_id值按顺序拼接成字符串
• 去重重复聚合行：外层DISTINCT用来消除窗口函数带来的重复行（窗口函数会给分组内每一行生成相同的聚合结果）

适配动态SQL场景的优化写法 #

针对你提到的100多列动态SQL场景，不需要重复编写完整的动态查询，可以用公共表表达式（CTE）简化代码，避免冗余：

WITH dynamic_data AS (
    SELECT /*SOME 100 COLUMN*/, LOC_ID_1, LOC_ID_2 FROM TABLE1
    UNION ALL
    SELECT /*SOME 100 COLUMN*/, LOC_ID_1, LOC_ID_2 FROM TABLE2
    UNION ALL
    SELECT /*SOME 100 COLUMN*/, LOC_ID_1, LOC_ID_2 FROM TABLE3
)
SELECT DISTINCT ID, key_code,
LISTAGG(loc_id,'||') WITHIN GROUP (ORDER BY loc_id) OVER (PARTITION BY ID) AS loc_id FROM (
    SELECT ID, key_code, LOC_ID_1 AS loc_id FROM dynamic_data
    UNION
    SELECT ID, key_code, LOC_ID_2 AS loc_id FROM dynamic_data
);

注意事项 #

• 字符串长度限制：LISTAGG()默认拼接结果不能超过VARCHAR2的最大长度（Oracle默认4000字节，12c及以上可扩展至32767字节），如果数据量过大，可改用XMLAGG替代处理超长字符串。
• 性能优化：若数据量极大，UNION的去重操作会有性能开销，建议先对动态结果集做过滤再进行后续处理。
• 空值处理：如果loc_id_1或loc_id_2存在空值，UNION会保留一个空值，若不需要可在子查询中添加WHERE loc_id IS NOT NULL过滤。

内容的提问来源于Stack Exchange，提问作者Anmol