由于业务需要对表中的数据进行行变列的处理,从网上查了下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
分享到:
相关推荐
行列转换,层级关系,oracle sys_connect_by_path的用法
通过实例比较了 SYS_CONNECT_BY_PATH 和 CONNECT_BY_ROOT 的异同,和返回树形的数据结构
今天无意间,看connect by的使用,看到了sys_connect_by_path的用法,算是给我一个另类的惊喜了,sys_connect_by_path(columnname, seperator) 也可以拼出串来,不过这个函数本身不是用来给我们做这个结果集连接用...
ORACLE中的sys_context函数
SYS_CONTEXT函数的用法,很好用的东东
Oracle的LOB(CLOB)大字段以及(SYS_LOB$$)清理.txt
重写oracle wm_contact 函数
Oracle_start_with_connect_by_prior_用法[文].pdf
Oracle中的USERENV和SYS_CONTEXT范例
Oracle聚合函数Oracle聚合函数Oracle聚合函数
ORA-04063: package body SYS.DBMS_REGISTRY_SYS has errors
oracle中的数查询,介绍的详细,有例子。
C#项目引用Oracle.ManagedDataAccess,实现对Oracle数据库的操作,包含增删改查,存储过程执行等
很多场合我们都会用到...sys_connect_by_path(字段名, 2个字段之间的连接符号),这里的连接符号不要使用逗号,oracle会报错,如果一定要用,可以使用replace替换一下,方法如下 REPLACE(字段名,原字符,’,’)。这个
oracle dbms_lob
oracle_课件oracle_课件oracle_课件oracle_课件oracle_课件oracle_课件oracle_课件oracle_课件oracle_课件oracle_课件oracle_课件
oracle函数查阅用文档,对于初学者比较方便,用熟了,就可以不用了
Oracle to_dOracle to_date() 用法细节.txtate() 用法细节.txOracle to_date() 用法细节.txtt
oracledb_exporter 是prometheus 的一个对Oracle监控的Exporter , Prometheus+Grafana 对Oracle实现监控