请问一条SQL语句，两个表联合查询的
请教一条SQL语句，两个表联合查询的
数据表结构：
表A
ID
1
2
3
表B
ID name
1 张三
2 李四
2 张三
3 王五
3 李四
期望的查询结果是
ID NAME
1 张三
2 李四||张三
3 王五||李四
联合条件是ID相等。
------解决方案--------------------
------解决方案--------------------
------解决方案--------------------
数据表结构：
表A
ID
1
2
3
表B
ID name
1 张三
2 李四
2 张三
3 王五
3 李四
期望的查询结果是
ID NAME
1 张三
2 李四||张三
3 王五||李四
联合条件是ID相等。
------解决方案--------------------
WITH TT AS
(SELECT 1 ID
FROM DUAL
UNION ALL
SELECT 2 ID
FROM DUAL
UNION ALL
SELECT 3 ID
FROM DUAL
UNION ALL
SELECT 4 ID
FROM DUAL),
T AS
(SELECT 1 ID, 'ZS' NM
FROM DUAL
UNION ALL
SELECT 2 ID, 'ZS' NM
FROM DUAL
UNION ALL
SELECT 2 ID, 'LS' NM
FROM DUAL
UNION ALL
SELECT 3 ID, 'ZS' NM
FROM DUAL
UNION ALL
SELECT 3 ID, 'WW' NM
FROM DUAL)
SELECT TT.ID, T3.NM
FROM TT,
(SELECT T2.ID, ltrim(max(SYS_CONNECT_BY_PATH(t2.NM, '
------解决方案--------------------
')), '
------解决方案--------------------
') NM
FROM (SELECT T.*, ROW_NUMBER() OVER(PARTITION BY ID ORDER BY ID) RN
FROM T) T2
connect by prior rn = rn - 1
and prior t2.id = t2.id
GROUP BY T2.ID) T3
WHERE TT.ID = T3.ID(+);
------解决方案--------------------
WITH A AS
(SELECT 1 ID FROM DUAL UNION ALL
SELECT 2 ID FROM DUAL UNION ALL
SELECT 3 ID FROM DUAL ),
B AS
(SELECT 1 ID, '张三' name FROM DUAL UNION ALL
SELECT 2, '李四' FROM DUAL UNION ALL
SELECT 2, '张三' FROM DUAL UNION ALL
SELECT 3, '王五' FROM DUAL UNION ALL
SELECT 3, '李四' FROM DUAL)
SELECT A.ID, REPLACE(WMSYS.WM_CONCAT(B.NAME), ',', '
------解决方案--------------------
')
FROM A, B
WHERE A.ID = B.ID(+)
GROUP BY A.ID
------解决方案--------------------
select b.id, b.name
from 表A a,
(select id, replace(wm_concat(name), ',', '
------解决方案--------------------
') as name
from 表B
group by id) b