create
table
t8 (i
int
,a
number
,b
varchar2
(
20
));
?
insert
into
? t8
values
(
1
,
2
,
'c'
);
insert
into
? t8
values
(
1
,
3
,
'x'
);
insert
into
? t8
values
(
1
,
4
,
'b'
);
insert
into
? t8
values
(
2
,
5
,
'd'
);
insert
into
? t8
values
(
2
,
6
,
'h'
);
insert
into
? t8
values
(
3
,
7
,
'j'
);
insert
into
? t8
values
(
3
,
8
,
'y'
);
insert
into
? t8
values
(
3
,
9
,
'l'
);
insert
into
? t8
values
(
3
,
10
,
'v'
);
?
commit
;
?
SQL> select * from t8;
?
???????? I????????? A B
---------- ---------- --------------------
???????? 1????????? 2 c
???????? 1????????? 3 x
???????? 1????????? 4 b
???????? 2????????? 5 d
???????? 2????????? 6 h
???????? 3????????? 7 j
???????? 3????????? 8 y
???????? 3????????? 9 l
???????? 3???????? 10 v
?
已選擇
9
行。
?
?
希望得到的結果：
?
I???? CHAR
---------- ----------
1??
c,x,b
2?? d,h
3?? j,y,l,v
?
?
?
方法一：
?
如果是
10g
，則可使用最簡單的方法：wm_sys.wm_concat函數(shù)
(wm_sys
不加也可
)
?
SQL> select i,wmsys.wm_concat(b) from t group by i;
?
???????? I WMSYS.WM_CONCAT(B)
---------- --------------------
????????
1 c,x,b
???????? 2 d,h
???????? 3 j,y,l,v
?
缺點：不能加排序功能，例如按
a
列逆序排列，需要在前一層排好序之后再使用wmsys.wm_concat
?
?
?
方法二：
?
9i
及
9i
以前的數(shù)據(jù)庫版本可使用
connect by
來實現(xiàn)子葉的連接：
?
select
i, ltrim(
max
(sys_connect_by_path(b,
','
)),
','
) b
?
from
(
select
i,
?????????????? b,
?????????????? a,
??????????????
min
(a) over(
partition
by
i) a_min,
?????????????? (row_number() over(
order
by
i, a)) +
?????????????? (dense_rank() over(
order
by
i)) numid
?????????
from
t8)
?
start
with
a = a_min
connect
by
numid -
1
=
prior
numid
?
group
by
i;
?
?
解釋一下這段
SQL
：
1
、最里面一層，對表
t8
進行處理，主要是列出了列
numid
用于等下的
connect by
操作，具體效果如下：
?
SQL> select i,b,a,min(a) over(partition by i) a_min,
? 2? (row_number() over(order by i, a)) a,
? 3? (dense_rank() over(order by i)) b,
? 4? (row_number() over(order by i, a)) +(dense_rank() over(order by i)) numid
? 5? from t8;
?
?? I B?????? A????? A_MIN??? A??? B????? NUMID
---- ---- ---- ---------- ---- ---- ----------
??
1 c
?????? 2????????? 2??? 1??? 1????????? 2
?? 1 x?????? 3????????? 2??? 2??? 1????????? 3
?? 1 b?????? 4????????? 2??? 3??? 1????????? 4
?? 2 d?????? 5????????? 5??? 4??? 2????????? 6
?? 2 h?????? 6????????? 5??? 5??? 2????????? 7
?? 3 j?????? 7????????? 7??? 6??? 3????????? 9
?? 3 y?????? 8????????? 7??? 7??? 3???????? 10
??
3 l
?????? 9????? ????7??? 8??? 3???????? 11
?? 3 v????? 10????????? 7??? 9??? 3???????? 12
?
9 rows selected
?
其中
row_mun
產生依次列表，
dense_rank
產生相同值相同排名，這樣通過
dense_rank
就可以區(qū)分出在哪里截斷
connect by
從
numid
列可以看出，每個相同的
i
對應一組序列，之后跳過一個數(shù)字后繼續(xù)形成序列
?
2
、通過
sys_connect_by_path
，以及
start
with
a = a_min
connect
by
numid -
1
=
prior
numid
來形成一個分支列表，結果如下：
?
?? I B
---- --------
?? 1 ,c
?? 1 ,c,x
?? 1 ,c,x,b
?? 2 ,d
?? 2 ,d,h
?? 3 ,j
?? 3 ,j,y
?? 3 ,j,y,l
?? 3 ,j,y,l,v
?
?
3
、通過
max
函數(shù)選出我們需要的每個
i
對應的最后一列
?
?? I B
---- --------
?? 1 ,c,x,b
?? 2 ,d,h
?? 3 ,j,y,l,v
?
4
、用
ltrim
或
substr
來去掉最前面的
“
,
”
?
?
?
方法三：
?
自己寫一個函數(shù)來處理：
?
create
or
replace
function
my_concat(n
number
)
return
varchar2
is
?
type
typ_cursor
is
ref
cursor
;
?v_cursor typ_cursor;
?v_temp
varchar2
(
10
);
?v_result
varchar2
(
4000
):=
''
;
?v_sql
varchar2
(
200
);
begin
?v_sql :=
'select b from t8 where i='
|| n ||
' order by a'
;
?
open
v_cursor
for
v_sql;
?
loop
???
fetch
v_cursor
into
v_temp;
???
exit
when
v_cursor
%
notfound
;
??? v_result := v_result ||
','
|| v_temp;
?
end
loop
;
?
return
substr(v_result,
2
);
end
;
?
?
SQL> select i,my_concat(i) from t8 group by i;
?
?? I MY_CONCAT(I)
---- --------------------------------------------------------------------------------
??
1 c,x,b
?? 2 d,h
?? 3 j,y,l,v
?
?
?
?
?
-The End-