-- 创建测试表
CREATE TABLE t_face_project (
ID NUMBER PRIMARY KEY,
plan_cert_id VARCHAR2(50),
project_name VARCHAR2(100)
);

CREATE TABLE t_face_project_module (
ID NUMBER PRIMARY KEY,
project_id NUMBER,
cert_id VARCHAR2(50),
module_name VARCHAR2(100)
);

-- 插入测试数据
INSERT INTO t_face_project (ID, plan_cert_id, project_name) VALUES (1, 'CERT001', '项目A');
INSERT INTO t_face_project (ID, plan_cert_id, project_name) VALUES (2, 'CERT002', '项目B');
INSERT INTO t_face_project (ID, plan_cert_id, project_name) VALUES (3, 'CERT003', '项目C');

INSERT INTO t_face_project_module (ID, project_id, cert_id, module_name) VALUES (1, 1, 'MOD001', '模块A1');
INSERT INTO t_face_project_module (ID, project_id, cert_id, module_name) VALUES (2, 1, 'MOD002', '模块A2');
INSERT INTO t_face_project_module (ID, project_id, cert_id, module_name) VALUES (3, 2, 'MOD003', '模块B1');
INSERT INTO t_face_project_module (ID, project_id, cert_id, module_name) VALUES (4, 3, 'MOD004', '模块C1');
INSERT INTO t_face_project_module (ID, project_id, cert_id, module_name) VALUES (5, 3, 'MOD005', '模块C2');

COMMIT;

-- 执行转换后的查询
SELECT
LISTAGG(cert_id, ',') WITHIN GROUP (ORDER BY cert_id) AS certIds,
LISTAGG(source_id, ',') WITHIN GROUP (ORDER BY source_id) AS sourceIds
FROM (
SELECT DISTINCT cert_id, source_id
FROM (
SELECT
TRIM(REGEXP_SUBSTR(cert_ids, '[^,]+', 1, LEVEL)) AS cert_id,
TRIM(REGEXP_SUBSTR(source_ids, '[^,]+', 1, LEVEL)) AS source_id
FROM (
SELECT
pro.plan_cert_id || ',' || NVL(mods.cert_id, '') AS cert_ids,
pro.ID || ',' || NVL(TO_CHAR(mods.ID), '') AS source_ids
FROM t_face_project pro
LEFT JOIN t_face_project_module mods ON pro.ID = mods.project_id
)
CONNECT BY LEVEL <= REGEXP_COUNT(cert_ids, ',') + 1
)
WHERE cert_id IS NOT NULL AND cert_id != ''
);

--试试看能满足要求么