表A
id type name
1 E AA
2 F
表B
id Aid name
1 2 BB
2 2 CC表B 是表A的子表
如果表A中的type是F,就需要根据Aid 去表B里找对应的name,然后拼接起来数据库是Oracle10G 需要查询的结果:Aid type name
1 E AA
2 F BB,CC
id type name
1 E AA
2 F
表B
id Aid name
1 2 BB
2 2 CC表B 是表A的子表
如果表A中的type是F,就需要根据Aid 去表B里找对应的name,然后拼接起来数据库是Oracle10G 需要查询的结果:Aid type name
1 E AA
2 F BB,CC
with a as(
select 1 id,'E' type,'AA' name from dual
union all
select 2,'F',null from dual
),b as(
select 1 id, 2 aid, 'BB' name from dual
union all
select 2,2,'CC' from dual
)
select a.id, a.type, wm_concat(nvl(a.name, b.name))
from a, b
where a.id = b.aid(+)
group by a.id, a.type;
ID TYPE WM_CONCAT(NVL(A.NAME,B.NAME))
---------- ---- --------------------------------------------------------------------------------
1 E AA
2 F BB,CC
SELECT '1' AS Aid, 'E' AS type,'AA' as name FROM dual
union all
SELECT '2' AS Aid, 'F' AS type,'' as name FROM dual
union all
SELECT '3' AS Aid, 'F' AS type,'' as name FROM dual
union all
SELECT '4' AS Aid, 'A' AS type,'FF' as name FROM dual
),
TABLE2 AS (
SELECT '1' AS id, '2' AS Aid,'BB' as name FROM dual
union all
SELECT '2' AS id, '2' AS Aid,'CC' as name FROM dual
union all
SELECT '3' AS id, '3' AS Aid,'DD' as name FROM dual
union all
SELECT '4' AS id, '3' AS Aid,'EE' as name FROM dual
)
SELECT T1.Aid,
T1.TYPE,
LISTAGG(nvl(T1.NAME, T2.NAME),',')WITHIN GROUP(ORDER BY id) AS NAME
FROM TABLE1 T1
LEFT JOIN TABLE2 T2 ON T1.Aid = T2.Aid
GROUP BY T1.Aid, T1.TYPE
用函数实现也可以
GROUP(
ORDER BY id) AS NAME FROM TABLE2 GROUP BY Aid) T2 ON T1.Aid = T2.Aid ORDER BY T1.Aid