如何实现以下查询
表 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
[其他解释]
select a.Aid, a.name, a.age, wm_concat(b.waihao,',') waihao
from a,b where a.aid=b.bid
group by a.Aid, a.name, a.age
[其他解释]
没人看了么
[其他解释]
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
[其他解释]
select t.Aid, t.name, t.age, wmsys.wm_concat(t.waihao,',') waihao
(select a.Aid ,a.name , a.age ,b.waihao
from a,b where a.aid=b.aid(+))t
group by t.Aid
[其他解释]
--SQL:
WITH a AS
(
SELECT 1 a, 'lily' b, 8 c FROM dual
UNION ALL
SELECT 2, 'lucy', 9 FROM dual
UNION ALL
SELECT 3, 'lilei', 9 FROM dual
),
T AS
(
SELECT 1 a, 2 b, 'meinv' c FROM dual
UNION ALL
SELECT 2, 2, 'luoli' FROM dual
UNION ALL
SELECT 3, 1, 'yujie' FROM dual
)
SELECT a.a,a.c,a.b,t.c
FROM
(SELECT b,max(substr(sys_connect_by_path(c,','),2)) c
FROM
(
SELECT a,b,c,row_number()over(PARTITION BY b ORDER BY a) rn FROM t
)
START WITH rn=1
CONNECT BY rn-1 = PRIOR rn AND b=PRIOR b
GROUP BY b) t,a
WHERE t.b(+)=a.a
--result:
18lilyyujie
29lucymeinv,luoli
39lilei
[其他解释]
大家理解错我的意思了 那个waihao字段是不固定的 不能用带入的来查询，如果我再新增那查询语句岂不是还得再改。。。我要的是那种不用改查询语句就能查到那种效果，不管对应的waihao 有多少个...
[其他解释]
參照
/****************************************************************************************
Oracle分拆合并表
整理人：中國風(Roy)
日期:2011.11.02
*****************************************************************************************/
/**--合并
--模拟数据
Col1 Col2
1 a
1 b
1 c
2 d
2 e
3 f
**/
/**--生成结果
COL1 COL2
1 a,b,c
2 d,e
3 f
**/
/**oracle10g以上版本字符串函数wmsys.wm_concat**/
/**方法1**/
with Tab
as
(
select 1 as Col1,'a' as Col2 from dual union all
select 1,'b' from dual union all
select 1,'c' from dual union all
select 2,'d' from dual union all
select 2,'e' from dual union all
select 3,'f' from dual
)
select
Col1,wmsys.wm_concat(Col2 ) as Col2
from tab group by Col1
/**oracle9i可以用connect by**/
/**方法2**/
with Tab
as
(
select 1 as Col1,'a' as Col2 from dual union all
select 1,'b' from dual union all
select 1,'c' from dual union all
select 2,'d' from dual union all
select 2,'e' from dual union all
select 3,'f' from dual
)
select
Col1,substr(max(sys_connect_by_path(Col2,',')),2) Col2
from
(select a.*,row_number()over(partition by Col1 order by Col1) rn from Tab a )
group by Col1 start with rn=1
connect by rn-1=prior rn and Col1=prior Col1
order by Col1;
/**--分拆
--模拟数据
Col1 Col2
1 a,b,c
2 d,e
3 f
**/
http://blog.csdn.net/roy_88/article/details/6930577
[其他解释]
说实话，没看懂。
[其他解释]
上面很多例子都能解决你的问题，。其中的wm_concat是10g以后版本出现的函数。可能3楼的例子楼主没有看懂才提出的这个疑问，在3楼使用的那些waihao不是SQL语句的内容，with ... as ()中的内容是用来生成测试数据的，你在实际使用中是不需要引用这部分代码的。
[其他解释]
哦，谢谢...