CREATE TABLE  "A" (
  id NUMBER NOT NULL,
  name VARCHAR2(255 BYTE)
)
 
INSERT INTO "A" VALUES ('1', '上海');
INSERT INTO "A" VALUES ('2', '北京');
INSERT INTO "A" VALUES ('3', '廣州');
INSERT INTO "A" VALUES ('4', '深圳');

 
CREATE TABLE  "B" (
  id NUMBER NOT NULL,
  aid VARCHAR2(255 BYTE)
)
 
INSERT INTO "B" VALUES ('1', '1,2,3');
INSERT INTO "B" VALUES ('2', '1,2');
INSERT INTO "B" VALUES ('3', '1,2,3,4');

select * from A where id in (select aid from B where id = 3)

	WITH 
    split_string AS (
		        SELECT   (select aid from B  where id = 3)AS string FROM dual
    )
SELECT 
    REGEXP_SUBSTR(string, '[^,]+', 1, LEVEL) AS aid
FROM 
    split_string
CONNECT BY
    REGEXP_SUBSTR(string, '[^,]+', 1, LEVEL) IS NOT NULL;

 select * from A  where id  in (	WITH 
    split_string AS (
		        SELECT   (select aid from B  where id = 3)AS string FROM dual
    )
SELECT 
    REGEXP_SUBSTR(string, '[^,]+', 1, LEVEL) AS aid
FROM 
    split_string
CONNECT BY
    REGEXP_SUBSTR(string, '[^,]+', 1, LEVEL) IS NOT NULL)

select LISTAGG(name,',') WITHIN GROUP (ORDER BY name) 關聯(lián)流程 from A where  id in (	WITH 
    split_string AS (
        SELECT   (select aid from B  where id = 3)AS string FROM dual
    )
SELECT 
    REGEXP_SUBSTR(string, '[^,]+', 1, LEVEL) AS split_value
FROM 
    split_string
CONNECT BY
    REGEXP_SUBSTR(string, '[^,]+', 1, LEVEL) IS NOT NULL)