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函数
(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
以前的数据库版本可使用
connect by
来实现子叶的连接：
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
就可以区分出在哪里截断
connect by
从
numid
列可以看出，每个相同的
i
对应一组序列，之后跳过一个数字后继续形成序列
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
函数选出我们需要的每个
i
对应的最后一列
I B
---- --------
1 ,c,x,b
2 ,d,h
3 ,j,y,l,v
4
、用
ltrim
或
substr
来去掉最前面的
“
,
”
方法三：
自己写一个函数来处理：
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-