表 A
Aid name age
1 lily 8
2 lucy 9
3 lilei 9
表 B
Bid Aid waihao
1 2 meinv
2 2 luoli
3 1 yujie
查询出得结果
Aid name age waihao
1 lily 8 yujie
2 lucy 9 meinv,louli
3 lilei 9
注意：外号有多的要用“，”分开
------解决方案--------------------
SELECT A.AID, A.NAME, A.AGE, WM_CONCAT(B.WAIHAO) WAIHAO
FROM A, B
WHERE A.AID = B.AID(+)
GROUP BY A.AID, A.NAME, A.AGE;
--测试
[SYS@myoracle] SQL>WITH A AS(
2 SELECT 1 aid,'lily' NAME ,8 age FROM dual UNION ALL
3 SELECT 2 aid,'lucy' NAME ,9 age FROM dual UNION ALL
4 SELECT 3 aid,'lilei' NAME ,9 age FROM dual
5 ),B AS(
6 SELECT 1 bid,2 aid,'meinv' waihao FROM dual UNION ALL
7 SELECT 2 bid,2 aid,'lucy' waihao FROM dual UNION ALL
8 SELECT 3 bid,1 aid,'lilei' waihao FROM dual
9 )SELECT A.AID, A.NAME, A.AGE, WM_CONCAT(B.WAIHAO) WAIHAO
10 FROM A, B
11 WHERE A.AID = B.AID(+)
12 GROUP BY A.AID, A.NAME, A.AGE
13 ;
AID NAME AGE WAIHAO
---------- ----- ---------- ----------
1 lily 8 lilei
2 lucy 9 meinv,lucy
3 lilei 9