个人认为写得比较好,转过来标记一下
表结构和数据如下(表名Test):
NO VALUE NAME
1 a 测试1
1 b 测试2
1 c 测试3
1 d 测试4
2 e 测试5
4 f 测试6
4 g 测试7
Sql语句:
select No,
ltrim(max(sys_connect_by_path(Value, ';')), ';') as Value,
ltrim(max(sys_connect_by_path(Name, ';')), ';') as Name
from (select No,
Value,
Name,
rnFirst,
lead(rnFirst) over(partition by No order by rnFirst) rnNext
from (select a.No,
a.Value,
a.Name,
row_number() over(order by a.No, a.Value desc) rnFirst
from Test a) tmpTable1) tmpTable2
start with rnNext is null
connect by rnNext = prior rnFirst
group by No;
检索结果如下:
NO VALUE NAME
1 a;b;c;d 测试1;测试2;测试3;测试4
2 e 测试5
4 f;g 测试6;测试7
简单解释一下那个Sql吧:
1、最内层的Sql(即表tmpTable1),按No和Value排序,并列出行号:
select a.No,
a.Value,
a.Name,
row_number() over(order by a.No, a.Value desc) rnFirst
from Test a
该语句结果如下:
NO VALUE NAME RNFIRST
1 d 测试4 1
1 c 测试3 2
1 b 测试2 3
1 a 测试1 4
2 e 测试5 5
4 g 测试7 6
4 f 测试6 7
2、外层的Sql(即表tmpTable2),根据No分区,取出当前行对应的下一条记录的行号字段:
select No,
Value,
Name,
rnFirst,
lead(rnFirst) over(partition by No order by rnFirst) rnNext
from (这里是tmpTable1的SQL) tmpTable1
lead(rnFirst):取得下一行记录的rnFirst字段
over(partition by No order by rnFirst) 按rnFirst排序,并按No分区,分区就是如果下一行的No字段与当前行的No字段不相等时,不取下一行记录显示
该语句结果如下:
NO VALUE NAME RNFIRST RNNEXT
1 d 测试4 1 2
1 c 测试3 2 3
1 b 测试2 3 4
1 a 测试1 4 NULL
2 e 测试5 5 NULL
4 g 测试7 6 7
4 f 测试6 7 NULL
3、最后就是最外层的sys_connect_by_path函数与start递归了
sys_connect_by_path(Value, ';')
start with rnNext is null
connect by rnNext = prior rnFirst
这个大概意思就是从rnNext为null的那条记录开始,递归查找,
如果前一记录的rnFirst字段等于当前记录的rnNext字段,就把2条记录的Value用分号连接起来,
大家可以先试试下面这个没有Max和Group的Sql:
select No,
sys_connect_by_path(Value, ';') as Value,
sys_connect_by_path(Name, ';') as Name
from (select No,
Value,
Name,
rnFirst,
lead(rnFirst) over(partition by No order by rnFirst) rnNext
from (select a.No,
a.Value,
a.Name,
row_number() over(order by a.No, a.Value desc) rnFirst
from Test a) tmpTable1) tmpTable2
start with rnNext is null
connect by rnNext = prior rnFirst
结果是:
NO VALUE NAME
1 ;a ;测试1
1 ;a;b ;测试1;测试2
1 ;a;b;c ;测试1;测试2;测试3
1 ;a;b;c;d ;测试1;测试2;测试3;测试4
2 ;e ;测试5
4 ;f ;测试6
4 ;f;g ;测试6;测试7
可以看到,每个No的最后一条记录就是我们要的了
所以在sys_connect_by_path外面套一个Max,再加个Group by No,得到的结果就是行转列的结果了
最后再加一个Ltrim,去掉最前面的那个分号,完成。
分享到:
相关推荐
行列转换,层级关系,oracle sys_connect_by_path的用法
通过实例比较了 SYS_CONNECT_BY_PATH 和 CONNECT_BY_ROOT 的异同,和返回树形的数据结构
今天无意间,看connect by的使用,看到了sys_connect_by_path的用法,算是给我一个另类的惊喜了,sys_connect_by_path(columnname, seperator) 也可以拼出串来,不过这个函数本身不是用来给我们做这个结果集连接用...
很多场合我们都会用到...sys_connect_by_path(字段名, 2个字段之间的连接符号),这里的连接符号不要使用逗号,oracle会报错,如果一定要用,可以使用replace替换一下,方法如下 REPLACE(字段名,原字符,’,’)。这个
介绍了将多行转为字符串的三种方案,并比较了三种方案的执行效率. 1.sys_connect_by_path + start with ... connect by ... prior + 分析函数 2.自定义Function/SP 3.使用 Oracle 10g 内置函数 wmsys.wm_concat
行列转换,sys_connect_by_path,row_number等函数的用法
oracle 递归调用 地区 ,用到的方法是 SYS_CONNECT_BY_PATH,可以扩展层级 名称
Oracle 数据库树形结构用法总结,例如SYS_CONNECT_BY_PATH 、START WITH . . . CONNECT BY . . .等具体语法介绍
1. 查询本节点及本节点以下的所有节点: ...SELECT RPAD( ' ', 2*(LEVEL-1), '-' ) || DEPNAME "DEPNAME",CONNECT_BY_ROOT DEPNAME "ROOT",CONNECT_BY_ISLEAF "ISLEAF",LEVEL ,SYS_CONNECT_BY_PATH(DEPNAM
单值函数多数处理单个或多个值但只返回单个值(SYS_CONNECT_BY_PATH除外)。 聚合函数多数处理多行并且各分组序列分别返回成一行。 分析函数多数处理多行并且每条记录均会有返回。 需要注意不同类型函数可...
11.9.2 Oracle中的SYS_CONNECT_BY_PATH()函数 11.9.3 My SQL Server和DB2中递归查询 附录A 常用数据库系统的安装和使用 A.1 DB2的安装和使用 A.2 MySQL的安装和使用 A.3 Oracle的安装和使用 A.4 ...
11.9.2 Oracle中的SYS_CONNECT_BY_PATH()函数 11.9.3 My SQL Server和DB2中递归查询 附录A 常用数据库系统的安装和使用 A.1 DB2的安装和使用 A.2 MySQL的安装和使用 A.3 Oracle的安装和使用 A.4 ...
11.9.2 Oracle中的SYS_CONNECT_BY_PATH()函数 11.9.3 My SQL Server和DB2中递归查询 附录A 常用数据库系统的安装和使用 A.1 DB2的安装和使用 A.2 MySQL的安装和使用 A.3 Oracle的安装和使用 A.4 ...
11.9.2 Oracle中的SYS_CONNECT_BY_PATH()函数 11.9.3 My SQL Server和DB2中递归查询 附录A 常用数据库系统的安装和使用 A.1 DB2的安装和使用 A.2 MySQL的安装和使用 A.3 Oracle的安装和使用 A.4 ...
export PATH=$PATH:$ORACLE_HOME/bin export LD_LIBRARY_PATH=$ORACLE_HOME/lib:/usr/lib export ORACLE_SID=cams export ORACLE_TERM=vt100 export ORA_NLS33=$ORACLE_HOME/ocommon/nls/admin/data...
11.9.2 Oracle中的SYS_CONNECT_BY_PATH()函数 11.9.3 My SQL Server和DB2中递归查询 附录A 常用数据库系统的安装和使用 A.1 DB2的安装和使用 A.2 MySQL的安装和使用 A.3 Oracle的安装和使用 A.4 ...
10.4.2 SYS_CONNECT_BY_PATH函数 279 10.4.3 CONNECT_BY_ROOT运算符 281 10.4.4 CONNECT_BY_ISCYCLE伪列和NOCYCLE参数 284 10.4.5 CONNECT_BY_ISLEAF伪列 287 10.5 小结 291 第11章 半联结和反联结 292 11.1 ...
ii 用sys用户授予system用户sysdba权限(其实是一个角色) iii 用system用户再次连接target database (3) 可以在rman里面停起数据库(停库状态需要隐式登录,不能带网络服务名) (4) DataFile tbs_apollo_...