/* ******************* 需求 ******************************************************************/

/* A表:A1 字段  数据为'1,2'和'3,4'                                                           */

/* B表:B1 字段  数据为'3,4'和'4,5'                                                           */

/* 怎么样的SQL才能将查询的结果放入                                                           */

/* C表:C1 字段  数值不能重复如A表和B表  的联合查询的结果C1字段数据应是'1,2,3,4'和'3,4,5'(有去重的操作) */

/* A1, B1, C1列的数据类型均为VARCHAR2                                                        */

--测试数据

CREATE TABLE TA(A1 VARCHAR2(10));

INSERT INTO TA VALUES('1,2');

INSERT INTO TA VALUES('3,4');

CREATE TABLE TB(B1 VARCHAR2(10));

INSERT INTO TB VALUES('3,4');

INSERT INTO TB VALUES('4,5');

COMMIT;

CREATE TABLE TC(C1 VARCHAR2(20));

CREATE TABLE TTMP(col1 VARCHAR2(50));

DELETE FROM ttmp;

--4,就是,号的个数 + 1（可以用函数计算出来的)

INSERT INTO ttmp

SELECT regexp_substr(col1, '[^,]+', 1, level) AS res from (select wm_concat(a1) col1 FROM TA)

connect by level <= 4;

INSERT INTO TC

SELECT wm_concat(col1) FROM (SELECT DISTINCT col1 FROM ttmp);

--对TB重复前面的操作

DELETE FROM ttmp;

INSERT INTO ttmp

SELECT regexp_substr(col1, '[^,]+', 1, level) AS res from (select wm_concat(b1) col1 FROM TB)

connect by level <= 4;

INSERT INTO TC

SELECT wm_concat(col1) FROM (SELECT DISTINCT col1 FROM ttmp);

SELECT * FROM TC;