转自 :http://www.ll19.com/log/2010/06/04/115.html
一个工作中遇到的ORACLE合并行,对合并结果排序的记录。本来简单的认为使用wmsys.wm_concat函数就行,结果出了一些小问题,解决起来还是挺折腾的,认真记录一下:
表eims_gwjk_dbcb_log部分记录如下:
SELECT * FROM eims_gwjk_dbcb_log t
注意选中的几行。现在想以WFID为准进行合并行的操作,一开始使用的是wmsys.wm_concat函数来进行合并行的操作。
SELECT t.wfid,
wmsys.wm_concat(dbcb) AS dbcb ,
wmsys.wm_concat(tacheid) AS tacheid,
wmsys.wm_concat(tacheno) AS tacheno,
wmsys.wm_concat(senduserid) AS senduserid
FROM eims_gwjk_dbcb_log t GROUP BY t.wfid;
下面是合并后的结果:
注意选中的行,和前面选中的四行对比。我的想法是合并行之后,合并行的字段都应该按照顺序排序。
之前选中的行:
wfid |
dbcb |
tacheid |
tacheno |
senduserid |
85652 |
CB |
0000003075 |
3 |
U75CB3 |
85652 |
CB |
0000003076 |
2 |
U76CB2 |
85652 |
DB |
0000003076 |
2 |
U76DB2 |
85652 |
DB |
0000003075 |
3 |
U75DB3 |
合并之后:
wfid |
dbcb |
tacheid |
tacheno |
senduserid |
85652 |
DB,DB,CB,CB |
0000003076,0000003076,0000003075,0000003075 |
2,3,3,2 |
U76DB2,U75CB3,U75DB3,U76CB2 |
通过senduserid字段可以看出来(其实这个字段的值是我更改了用来检测合并之后字段顺序用的,对应了前面几个字段的值),几个合并行的字段顺序完全乱了,并没有按照预想的结果排列。
即使排序后合并:
SELECT t.wfid,
wmsys.wm_concat(dbcb) AS dbcb ,
wmsys.wm_concat(tacheid) AS tacheid,
wmsys.wm_concat(tacheno) AS tacheno,
wmsys.wm_concat(senduserid) AS senduserid
FROM (SELECT * FROM eims_gwjk_dbcb_log ORDER BY wfid,dbcb,tacheid,tacheno,senduserid) t GROUP BY t.wfid;
顺序也不对。还是说我不会用wmsys.wm_concat这个函数如何排序合并字段,反正搜了很多都没有解决方法。
没办法用的另一种合并行的方法,首先看这个语句:
SELECT wfid,dbcb,tacheid,tacheno,senduserid,
--按照wfid分组 每组的senduserid进行排序
(row_number() over(partition BY wfid ORDER BY senduserid)) rn
FROM eims_gwjk_dbcb_log
同注释,按照wfid分组,每组的senduserid进行排序别名RN。之后根据WFID和RN用sys_connect_by_path函数对数据进行合并:
SELECT wfid,(substr(sys_connect_by_path(dbcb,','),2)) AS dbcb,
(substr(sys_connect_by_path(tacheid,','),2)) AS tacheid,
(substr(sys_connect_by_path(tacheno,','),2)) AS tacheno,
(substr(sys_connect_by_path(senduserid,','),2)) AS senduserid,
level AS curr_level
FROM(
SELECT wfid,dbcb,tacheid,tacheno,senduserid,
--按照wfid分组 每组的senduserid进行排序
(row_number() over(partition BY wfid ORDER BY senduserid)) rn
FROM eims_gwjk_dbcb_log
)
connect BY wfid = prior wfid AND rn-1 = prior rn;
数据的合并类似于递归合并,我们只需要curr_level最高的那行,即合并了所有相关数据的那行,之后的语句:
SELECT * FROM (
--我们只需要curr_level最高的那行,即是已经完全合并好数据的那行,之后排序取得curr_level最高的行
SELECT wfid,dbcb,tacheid,tacheno,senduserid,
row_number() over(partition BY wfid ORDER BY wfid,curr_level DESC) c_level
FROM (
--根据WFID和RN用sys_connect_by_path函数对数据进行合并
SELECT wfid,(substr(sys_connect_by_path(dbcb,','),2)) AS dbcb,
(substr(sys_connect_by_path(tacheid,','),2)) AS tacheid,
(substr(sys_connect_by_path(tacheno,','),2)) AS tacheno,
(substr(sys_connect_by_path(senduserid,','),2)) AS senduserid,
level AS curr_level
FROM(
SELECT wfid,dbcb,tacheid,tacheno,senduserid,
--按照wfid分组 每组的senduserid进行排序
(row_number() over(partition BY wfid ORDER BY senduserid)) rn
FROM eims_gwjk_dbcb_log
)
connect BY wfid = prior wfid AND rn-1 = prior rn
))
WHERE c_level = 1
对比数据排序正常! = =
本来个人认为很EASY的一个事情最后确搞得如此复杂,或者说其实还是有对wmsys.wm_concat的合并排序方法?起码我搜索半天也没找到,最后用的这个方法我感觉还是,以后数据多了性能上的问题(但MS sys_connect_by_path 函数性能其实不错?)。
顺带对使用的函数进行一个总结:
-
Start with...Connect By
Start with...Connect By子句递归查询一般用于一个表维护树形结构的应用。(即递归查询)
一个使用实例,创建示例表:
CREATE TABLE TBL_TEST
(ID NUMBER,
NAME VARCHAR2(100 BYTE),
PID NUMBER DEFAULT 0);
插入测试数据:
INSERT INTO TBL_TEST(ID,NAME,PID) VALUES('1','10','0');
INSERT INTO TBL_TEST(ID,NAME,PID) VALUES('2','11','1');
INSERT INTO TBL_TEST(ID,NAME,PID) VALUES('3','20','0');
INSERT INTO TBL_TEST(ID,NAME,PID) VALUES('4','12','1');
INSERT INTO TBL_TEST(ID,NAME,PID) VALUES('5','121','2');
从Root往树末梢递归 (取全部“子孙”集):
SELECT * FROM TBL_TEST
start WITH id=1
connect BY prior id = pid
ORDER BY pid
从末梢往树ROOT递归(取全部“父爷”集):
SELECT * FROM TBL_TEST
start WITH id=5
connect BY prior pid = id
ORDER BY id
-
Group By聚合语句
Group By聚合语句,必须配合相关的例如sum,max,min等函数使用:
SELECT * FROM eims_gwjk_dbcb_log t
WHERE t.wfid = 85652
计算wfid=85652的tacheno的总和:
SELECT wfid,sum(tacheno) FROM eims_gwjk_dbcb_log
WHERE wfid = 85652
GROUP BY wfid;
-
min max
即取得最大和最小值:
SELECT max(senduserid) FROM eims_gwjk_dbcb_log
--U76DB2
SELECT min(senduserid) FROM eims_gwjk_dbcb_log
--U123
-
三个"评价"函数
这三个分析函数都可以在各个分组内从1开始排序。
ROW_NUMBER()是没有重复值的,可以利用它实现分页显示。
DENSE_RANK() 是连续排序,有两个第二名时仍然跟着第三名。
RANK()是跳跃排序,有两个第二名时接下来就是第四名(同样是在各个分组内)。
实例:
SELECT tacheid,
(ROW_NUMBER() over(ORDER BY tacheid)) rn
FROM eims_gwjk_dbcb_log t
tacheid rn
0000001864 1
0000001864 2
0000001889 3
0000001889 4
0000001911 5
0000001911 6
0000001911 7
0000001911 8
0000001918 9
0000001930 10
0000002896 11
SELECT tacheid,
(DENSE_RANK() over(ORDER BY tacheid)) rn
FROM eims_gwjk_dbcb_log t
tacheid rn
0000001864 1
0000001864 1
0000001889 2
0000001889 2
0000001911 3
0000001911 3
0000001911 3
0000001911 3
0000001918 4
0000001930 5
0000002896 6
SELECT tacheid,
(RANK() over(ORDER BY tacheid)) rn
FROM eims_gwjk_dbcb_log t
tacheid rn
0000001864 1
0000001864 1
0000001889 3
0000001889 3
0000001911 5
0000001911 5
0000001911 5
0000001911 5
0000001918 9
0000001930 10
0000002896 11
更多的使用例如:row_number() over ([partition by col1] order by col2) ) as 别名
表示根据col1分组,在分组内部根据col2排序,而这个“别名”的值就表示每组内部排序后的顺序编号(组内连续的唯一的), [partition by col1] 可省略。
SELECT dbcb,tacheid,
(RANK() over(partition BY dbcb ORDER BY tacheid)) rn
FROM eims_gwjk_dbcb_log t
先按照dbcb分组,后按照tacheid排序(即排序)即每个组内自己排序,而不是整个排序。
-
level
LEVEL查询数据所对应的级,level仅仅用于在对表执行层次树遍历的select语句中,个人理解遍历的层次越多,级别越大。
SELECT wfid,(substr(sys_connect_by_path(dbcb,','),2)) AS dbcb,
(substr(sys_connect_by_path(tacheid,','),2)) AS tacheid,
(substr(sys_connect_by_path(tacheno,','),2)) AS tacheno,
(substr(sys_connect_by_path(senduserid,','),2)) AS senduserid,
level AS curr_level
FROM(
SELECT wfid,dbcb,tacheid,tacheno,senduserid,
(row_number() over(partition BY wfid ORDER BY senduserid)) rn
FROM eims_gwjk_dbcb_log
)
connect BY wfid = prior wfid AND rn-1 = prior rn
-
sys_connect_by_path
sys_connect_by_path函数主要作用是可以把一个父节点下的所有子节点通过某个字符进行区分,然后连接在一个列中显示。这个函数使用之前必须先建立一个树,例如:
SELECT level ,sys_connect_by_path( id , '/' )
FROM test
start WITH pid= 1
connect BY prior pid = id ;
转自:http://www.ll19.com/log/2010/06/04/115.html#
分享到:
相关推荐
Oracle多行记录合并/连接/聚合字符串的几种方法
多行记录合并 采用的是方法4,希望对你们有用
在程序设计过程中,往往遇到比较两个记录集的差异。如,判断原来传入的订单资料与后来传入的订单资料之间的差异,并且将差异的...本文主要论述利用ORACLE的MINUS函数和OVER函数,直接通过视图实现两个记录集的比较。
NULL 博文链接:https://czjxdm.iteye.com/blog/466948
1.被集合字段范围小且固定型 灵活性 性能 难度 2.固定表固定字段函数法 灵活性 性能 难度 3.灵活表函数法 灵活性 性能 难度 4.一条SQL法 灵活性
怎么合并多行记录的字符串,一直是oracle新手喜欢问的SQL问题之一,关于这个问题的帖子我看过不下30个了,现在就对这个问题,进行一个总结。
oracle连续重复行去重,连续重复行合并。 要将连续n条记录中,id和 dno均相同的记录合并,合并后开始时间为第一条记录开始时间,结束时间为最后一条记录结束时间。
Oracle关系型数据库管理系统是世界上流行的关系数据库,它是一个极其强大、灵活和复杂的系统,本文向大家介绍使用SQL查两个Oracle数据表查询的相同数据的方法。第一种方法:利用操作符intersect,intersect操作符...
您可能感兴趣的文章:常用的Oracle doc命令(收藏)Oracle 多行记录合并/连接/聚合字符串的几种方法Oracle中字符串连接的实现方法php连接oracle数据库及查询数据的方法plsql连接oracle数据库报ora 12154错
对Oracle共享池和缓冲区中的Sql必须要大小写都完全用上才能够匹配上 2.顺序问题 Oracle按照从右到左的顺序对数据表进行解析。因此From最后面的表为基础表,一般要选择记录数最少的表作为基础表。 对于Where...
Oracle 实例 目标 3-2 初始化参数文件 3-3 PFILE initSID.ora 3-5 PFILE 例子 3-6 SPFILE spfileSID.ora 3-7 创建一个 SPFILE 3-8 SPFILE 例子 3-9 Oracle 管理文件 3-10 Oracle 管理文件例子 3-11 打开一个数据库 ...
2.3.1为服务器配置4个网卡 2.3.2安装Linux操作系统 2.3.3挂载iSCSI磁盘 2.3.4配置udev固定iSCSI磁盘设备名称 2.3.5配置服务器的图形化环境 2.4 RAC运行环境安装前检查 2.4.1服务器检查 2.4.2存储检查 2.4.3...
2. oracle11G自带一个卸载批处理\app\Administrator\product\11.2.0\dbhome_1\deinstall\deinstall.bat 3. 运行该批处理程序将自动完成oracle卸载工作,最后手动删除\app文件夹(可能需要重启才能删除) 4. 运行...
自己写的数据库装载工具,平时工作中用于装载大文本文件到数据库表,也能直接装载excel,不过excel只能使用第一个sheet,且不支持合并的单元格。 jdk要求版本1.6及以上。 使用方法: java -jar lynload.jar,即可看到...
第13章 当一个查询依赖于另一个查询时 第14章 一些复杂的技术 第15章 更改数据:插入﹑更新﹑合并和删除 第16章 decode 和case:sql中的if-then-else 第17章 创建和管理表、视图、索引、群集和序列 第18...
第13章 当一个查询依赖于另一个查询时 13.1 高级子查询 13.1.1 相关子查询 13.1.2 并列的逻辑测试 13.1.3 EXISTS及其相关子查询的使用 13.2 外部连接 13.2.1 Oracle9i以前版本中的外部连接的语法 13.2.2 现在的外部...
第13章 当一个查询依赖于另一个查询时 13.1 高级子查询 13.1.1 相关子查询 13.1.2 并列的逻辑测试 13.1.3 EXISTS及其相关子查询的使用 13.2 外部连接 13.2.1 Oracle9i以前版本中的外部连接的语法 13.2.2 现在的外部...