由于业务需要对表中的数据进行行变列的处理,从网上查了下sys_connect_by_path的用法,备忘
=============Begin==============
表test 数据
id(varchar2) mc(varchar2)
1 111111
1 222222
2 111111
2 222222
3 111111
3 222222
3 333333
select id,mc,row_number() over(partition by id order by id) rn_by_id,
row_number() over (order by id) + id rn from test;
利用分析函数,构造两列,做为连接的条件:按照id分组,RN-1等于PRIOR RN作为条件连接。
ID MC RN_BY_ID RN
---------- -------------------------------------------------- ---------- ----------
1 11111 1 2
1 22222 2 3
2 11111 1 5
2 22222 2 6
3 11111 1 8
3 22222 2 9
3 33333 3 10
select id,ltrim(max(sys_connect_by_path(mc,';')),';') add_mc from (
select id,mc,row_number() over(partition by id order by id) rn_by_id,
row_number() over (order by id) + id rn from test
)
start with rn_by_id = 1 connect by rn - 1 = prior rn
group by id
order by id;
另用sys_connect_by_path函数实现字符串的连接,把最左边的分号去掉,即得到我们想要的结果
(注意mc字段里不能包含';'字符,否则会出错)
ID ADD_MC
---------- --------------------------------------------------------------------------------
1 11111;22222
2 11111;22222
3 11111;22222;33333
=============End==============
现有表T1:
a(varchar2) b(varchar2) c(varchar2) d(varchar2)
aa01 b01 1001 d1
aa01 b01 1001 d2
aa01 b01 1001 d3
aa01 b03 1001 d1
aa01 b03 1001 d2
aa01 b03 1001 d3
aa01 b005 1001 d2
aa01 b005 1001 d1
aa01 b005 1001 d3
需要得到效果是按a,b,c 来分组,得到d的连接值
由于值不是数字,不能直接加,用聚合函数dense_rank()来构造分组连接条件:
SELECT a, b, c, max(substr(sys_connect_by_path(d, ', '), 3)) as dn
FROM (SELECT a, b, c, d,
dense_rank() over (order by a, b, c) + row_number() over (order by a, b, c) as rnid,
row_number() over (partition by a, b, c order by a, b, c) as id
FROM T1 ) start with id = 1 connect by rnid - 1 = prior rnid
GROUP BY a,b,c
a b c dn
------ -------- --------- -------------
aa01 b01 1001 d1, d2, d3
aa01 b03 1001 d1, d2, d3
aa01 b003 1001 d1, d2, d3
=======================================
关于聚合函数 dense_rank() 及 rank()的说明:
对给定的参数值在设定的排序查询中计算出其排序值。
这些参数必须是常数或常值表达式,且必须和ORDER BY子句中的字段个数、位置、类型完全一致。
语法:
RANK ( expr [, expr]... ) WITHIN GROUP
( ORDER BY
expr [ DESC | ASC ] [NULLS { FIRST | LAST }]
[, expr [ DESC | ASC ] [NULLS { FIRST | LAST }]]...
)
两者的区别:
dence_rank在并列关系是,相关等级不会跳过。rank则跳过
参考:
表
A B C
a liu wang
a jin shu
a cai kai
b yang du
b lin ying
b yao cai
b yang 99
当rank时为:
select m.a,m.b,m.c,rank() over(partition by a order by b) liu from test m
A B C LIU
a cai kai 1
a jin shu 2
a liu wang 3
b lin ying 1
b yang du 2
b yang 99 2
b yao cai 4
而如果用dense_rank时为:
select m.a,m.b,m.c,dense_rank() over(partition by a order by b) liu from test m
A B C LIU
a cai kai 1
a jin shu 2
a liu wang 3
b lin ying 1
b yang du 2
b yang 99 2
b yao cai 3