原文传送门:http://topic.csdn.net/u/20091028/10/f2012eb6-36b7-4a19-b8ef-52ce95401a1d.html
现有表
A:
taid
1
2
3
...
B:
tbid taid seq_no code
1 1 1 AAA
2 1 2 BBB
3 1 3 CCC
4 2 1 XXX
5 2 2 YYY
B.taid reference A.taid
如何通过sql语句得到 试图C
taid codestr
1 AAA-BBB-CCC
2 XXX-YYY
Oracle 10g
- select a.taid,replace(wm_concat(b.code),',','-')codestr
- from a,b
- where a.taid=b.taid
- group by a.taid
Oracle 9i
- select taid,substr(max(sys_connect_by_path(code,'-')),2)codestr
- from(
- select a.taid,b.code,
- row_number()over(partition by a.taid order by b.tbid)rn
- from a,b
- where a.taid=b.taid)
- connect by prior taid=taid and prior rn=rn-1
- start with rn=1
- group by taid