当前位置:首页 > 开发 > 数据库 > 正文

Oracle实用功能之分组后列合并

发表于: 2014-08-28   作者:seandeng888   来源:转载   浏览:
摘要: 1       实例解析 由于业务需求需要对表中的数据进行分组后进行合并的处理,鉴于Oracle10g没有现成的函数实现该功能,且该功能如若用JAVA代码实现会比较复杂,因此,特将SQL语言的实现方式分享出来,希望对大家有所帮助。如下: 表test 数据如下: ID,SUBJECTCODE,DIMCODE,VALUE 1&nbs

1       实例解析

由于业务需求需要对表中的数据进行分组后进行合并的处理,鉴于Oracle10g没有现成的函数实现该功能,且该功能如若用JAVA代码实现会比较复杂,因此,特将SQL语言的实现方式分享出来,希望对大家有所帮助。如下:

test 数据如下:

ID,SUBJECTCODE,DIMCODE,VALUE

1 , 111111           ,A           ,10000

2,  222222           ,A           ,20000

3,  111111           ,B           ,30000

4,  222222           ,B           ,40000

5,  111111           ,C           ,60000

6,  222222           ,C           ,70000

7,  333333           ,A           ,80000

 实现方式如下:

select SUBJECTCODE,ltrim(max(sys_connect_by_path(VALUE,',')),',') AS VALUES from (
select SUBJECTCODE,VALUE,row_number() over(partition by SUBJECTCODE,order by SUBJECTCODE) rn,
from test
)
start with rn = 1 connect by rn - 1 = prior rn AND SUBJECTCODE=PRIOR SUBJECTCODE
group by SUBJECTCODE
order by SUBJECTCODE;

结果如下:

SUBJECTCODE ,VALUES

111111      10000, 30000, 60000

222222       20000,40000,70000

333333      80000

下面对如上实现的所用到的Start with connect by prior,SYS_CONNECT_BY_PATH, row_number() OVER进行介绍。

2       Start with connect by prior语句  

SELECT命令中使用CONNECT BYSTART WITH子句可以查询表中的树型结构关系。其命令格式如下:

SELECT… …

CONNECT BY {PRIOR列名1=列名2|列名1=PRIOR裂名2}

[START WITH]

其中:CONNECT BY子句说明每行数据将是按层次顺序检索,并规定将表中的数据连入树型结构的关系中。PRIOR运算符必须放置在连接关系的两列中某一个的前面。对于节点间的父子关系,PRIOR运算符在一侧表示父节点,在另一侧表示子节点,从而确定查找树结构的顺序是自顶向下还是自底向上。在连接关系中,除了可以使用列名外,还允许使用列表达式。START WITH子句为可选项,用来标识哪个节点作为查找树型结构的根节点。若该子句被省略,则表示所有满足查询条件的行作为根节点。

Start with...Connect By子句递归查询一般用于一个维护树形结构的应用。

3       SYS_CONNECT_BY_PATH

看了上面的内容,相信大家都能看懂下面的语句。

SELECT ename 
FROM scott.emp 
START WITH ename = 'KING'
CONNECT BY PRIOR empno = mgr;
得到结果为: 
KING   
JONES  
 
SCOTT  
 
ADAMS  
 
FORD  
 
SMITH  
 
BLAKE  
 
ALLEN  
 
WARD  
 
MARTIN  
 
TURNER  
 
JAMES 
而:
SELECT SYS_CONNECT_BY_PATH(ename, '>') "Path"   
 
FROM scott.emp   
 
START WITH ename = 'KING'   
 
CONNECT BY PRIOR empno = mgr;  
 
--
得到结果为:   
>KING  
 
>KING>JONES  
 
>KING>JONES>SCOTT  
 
>KING>JONES>SCOTT>ADAMS  
 
>KING>JONES>FORD  
 
>KING>JONES>FORD>SMITH  
 
>KING>BLAKE  
 
>KING>BLAKE>ALLEN  
 
>KING>BLAKE>WARD  
 
>KING>BLAKE>MARTIN  
 
>KING>BLAKE>TURNER  
 
>KING>BLAKE>JAMES  
 
>KING>CLARK  
 
>KING>CLARK>MILLER 
 

综上所述:SYS_CONNECT_BY_PATH函数的第一个参数是形成树形式的字段,第二个参数是父级和其子级分隔显示用的分隔符!

4       row_number() OVER

row_number() OVER (PARTITION BY COL1 ORDER BY COL2) 表示根据COL1分组,在分组内部根据COL2排序,而此函数计算的值就表示每组内部排序后的顺序编号

示例: 
COL1   COL2     row_num 
A      1700     1 
A      1800     2 
B      1085     1 
B      1710     2

Oracle实用功能之分组后列合并

  • 0

    开心

    开心

  • 0

    板砖

    板砖

  • 0

    感动

    感动

  • 0

    有用

    有用

  • 0

    疑问

    疑问

  • 0

    难过

    难过

  • 0

    无聊

    无聊

  • 0

    震惊

    震惊

编辑推荐
表名字:mytable --用sql语句,不用临时表,存储过程实现 update (select id,SORT from mytable) a
---------------------------------------------------------------------------------------------
oracle分析函数row_number() over()使用 row_number() over ([partition by col1] order by col2) )
以前在开发的时候遇到过一个需求,就是要按照某一列进行分组后取前几条数据,今天又有同事碰到了,
以前在开发的时候遇到过一个需求,就是要按照某一列进行分组后取前几条数据,今天又有同事碰到了,
最近用到数据的的字符连接 || 在此做个笔记 2个字段用||连接起来,如果要加分隔符号。如下面 字段A|
在项目开发中,有时会碰到将列记录合并为一行的情况,例如根据地区将人员姓名合并,或根据拼音首字
在项目开发中,有时会碰到将列记录合并为一行的情况,例如根据地区将人员姓名合并,或根据拼音首字
创建表及插入数据 If OBJECT_ID(N'Demo') Is Not Null Begin Drop Table Demo End Else Begin Creat
问题出处: http://fayaa.com/tiku/view/114/ 2个有序数组求合并后的中位数 第一步:假设两个有序数
版权所有 IT知识库 CopyRight © 2009-2015 IT知识库 IT610.com , All Rights Reserved. 京ICP备09083238号