select wm_concat(TB2.NAME) from (select REGEXP_SUBSTR(TID, '[^,]+', 1, LEVEL) COL1 from TB1 CONNECT BY LEVEL <= REGEXP_COUNT(TID, ',') + 1) TB11 LEFT JOIN TB2 ON TB11.COL1 = TB2.ID
如果不支持用wm_concat,可以选择以下语句: select ltrim(max(sys_connect_by_path(t3.name, ',')), ',') from (select rownum rn, t1.name from tb2 t1, (select REGEXP_SUBSTR(t.id, '[^,]+', 1, LEVEL) STR from tb1 t CONNECT BY LEVEL <= REGEXP_COUNT(t.id, ',') + 1) t2 where t1.id = t2.str) t3 start with rn = 1 connect by prior rn = rn - 1;
额你这个只适用于一条的,如果我是查列表出来就不行了。TB1肯定是很多行的,每行的TID都不一样。
修改后的:SELECT LTRIM(MAX(SYS_CONNECT_BY_PATH(T3.NAME, ',')), ',') FROM (SELECT ROW_NUMBER() OVER(PARTITION BY T2.RNUM ORDER BY T1.NAME) RN, T1.NAME, T2.RNUM FROM TB2 T1, (SELECT T.RNUM, REGEXP_SUBSTR(T.ID, '[^,]+', 1, LEVEL) STR FROM (SELECT T.ID, ROWNUM RNUM FROM TB1 T) T CONNECT BY LEVEL <= (LENGTH(T.ID) - LENGTH(REPLACE(T.ID, ',', ''))) / LENGTH(',') + 1 AND PRIOR RNUM = RNUM AND PRIOR DBMS_RANDOM.VALUE IS NOT NULL) T2 WHERE T1.ID = T2.STR) T3 START WITH T3.RN = 1 CONNECT BY PRIOR RN = RN - 1 AND PRIOR T3.RNUM = T3.RNUM GROUP BY T3.RNUM;
你第一次给我们的数据TB1和TB2刚好相反啊, 如下:SELECT T3.ID, LTRIM(MAX(SYS_CONNECT_BY_PATH(T3.NAME, ',')), ','), T3.MEMO FROM (SELECT ROW_NUMBER() OVER(PARTITION BY T2.RNUM ORDER BY T1.NAME) RN, T1.NAME, T2.RNUM, T2.ID, T2.MEMO FROM TB1 T1, (SELECT T.ID, T.RNUM, REGEXP_SUBSTR(T.TB1ID, '[^,]+', 1, LEVEL) STR, T.MEMO FROM (SELECT T.ID, T.TB1ID, ROWNUM RNUM, T.MEMO FROM TB2 T) T CONNECT BY LEVEL <= (LENGTH(T.TB1ID) - LENGTH(REPLACE(T.TB1ID, ',', ''))) / LENGTH(',') + 1 AND PRIOR RNUM = RNUM AND PRIOR DBMS_RANDOM.VALUE IS NOT NULL) T2 WHERE T1.ID = T2.STR) T3 START WITH T3.RN = 1 CONNECT BY PRIOR RN = RN - 1 AND PRIOR T3.RNUM = T3.RNUM GROUP BY T3.RNUM, T3.ID, T3.MEMO; 还不行????
from (select REGEXP_SUBSTR(TID, '[^,]+', 1, LEVEL) COL1
from TB1
CONNECT BY LEVEL <= REGEXP_COUNT(TID, ',') + 1) TB11
LEFT JOIN TB2
ON TB11.COL1 = TB2.ID
select ltrim(max(sys_connect_by_path(t3.name, ',')), ',')
from (select rownum rn, t1.name
from tb2 t1,
(select REGEXP_SUBSTR(t.id, '[^,]+', 1, LEVEL) STR
from tb1 t
CONNECT BY LEVEL <= REGEXP_COUNT(t.id, ',') + 1) t2
where t1.id = t2.str) t3
start with rn = 1
connect by prior rn = rn - 1;
FROM (SELECT ROW_NUMBER() OVER(PARTITION BY T2.RNUM ORDER BY T1.NAME) RN,
T1.NAME,
T2.RNUM
FROM TB2 T1,
(SELECT T.RNUM, REGEXP_SUBSTR(T.ID, '[^,]+', 1, LEVEL) STR
FROM (SELECT T.ID, ROWNUM RNUM FROM TB1 T) T
CONNECT BY LEVEL <=
(LENGTH(T.ID) - LENGTH(REPLACE(T.ID, ',', ''))) /
LENGTH(',') + 1
AND PRIOR RNUM = RNUM
AND PRIOR DBMS_RANDOM.VALUE IS NOT NULL) T2
WHERE T1.ID = T2.STR) T3
START WITH T3.RN = 1
CONNECT BY PRIOR RN = RN - 1
AND PRIOR T3.RNUM = T3.RNUM
GROUP BY T3.RNUM;
ID NAME
1 NAME1
2 NAME2
3 NAME3
4 NAME4TB2
ID TB1ID MEMO
1 1,2 ABCDEFG
2 1,2,3 DDDDDDD
3 3,4 EEEEEEE我想查询出TB2里面的所有数据列表 得到这样的结果
1 NAME1,NAME2 ABCDEFG
2 NAME1,NAME2,NAME3 DDDDDDD
3 NAME3,NAME4 EEEEEEE应该怎么写?
如下:SELECT T3.ID, LTRIM(MAX(SYS_CONNECT_BY_PATH(T3.NAME, ',')), ','), T3.MEMO
FROM (SELECT ROW_NUMBER() OVER(PARTITION BY T2.RNUM ORDER BY T1.NAME) RN,
T1.NAME,
T2.RNUM,
T2.ID,
T2.MEMO
FROM TB1 T1,
(SELECT T.ID,
T.RNUM,
REGEXP_SUBSTR(T.TB1ID, '[^,]+', 1, LEVEL) STR,
T.MEMO
FROM (SELECT T.ID, T.TB1ID, ROWNUM RNUM, T.MEMO FROM TB2 T) T
CONNECT BY LEVEL <=
(LENGTH(T.TB1ID) -
LENGTH(REPLACE(T.TB1ID, ',', ''))) / LENGTH(',') + 1
AND PRIOR RNUM = RNUM
AND PRIOR DBMS_RANDOM.VALUE IS NOT NULL) T2
WHERE T1.ID = T2.STR) T3
START WITH T3.RN = 1
CONNECT BY PRIOR RN = RN - 1
AND PRIOR T3.RNUM = T3.RNUM
GROUP BY T3.RNUM, T3.ID, T3.MEMO;
还不行????
ID NAME
1 NAME1
2 NAME2
3 NAME3
4 NAME4TB2
ID TB1ID TB1ID2 TB1ID3 TB1ID4 MEMO
1 1,2 2,3,4 3 3,4 ABCDEFG
2 1,2,3 1,2 1,2 2,3 DDDDDDD
3 3,4 2,3 2,3,4 1,4 EEEEEEE得到这样的结果
1 NAME1,NAME2 NAME2,NAME3,NAME4 NAME3 NAME3,NAME4 ABCDEFG
2 NAME1,NAME2,NAME3 NAME1,NAME2 NAME1,NAME2 NAME2,NAME3 DDDDDDD
3 NAME3,NAME4 NAME2,NAME3 NAME2,NAME3,NAME4 NAME1,NAME2 EEEEEEE
不过有一种比较笨的方法是:TB1ID2,TB1ID3也像TB1ID1一样求出相同的效果作为一个临时结果集,然后再通过ID对几个子查询的结果集进行连接查询就能达到你要的效果。暂时没想到好的方法,有的话会告诉你