oracle主从关系表查询,Oracle 主从表联合查询

表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

*********************************************************

WITH TABLE1 AS(

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 BY】 用函数实现也可以

SELECT T1.NAME || T2.NAME AS NAME ,T1.* FROM TABLE1 T1 LEFT JOIN (SELECT Aid,LISTAGG(NAME, ',') WITHIN

GROUP(

ORDER BY id) AS NAME FROM TABLE2 GROUP BY Aid) T2 ON T1.Aid = T2.Aid ORDER BY T1.Aid