关于oracle多条记录合并为一条记录的方法

时间:2022-11-29 00:27:23

今天正还有同学问了一个问题,怎么把两条不同的记录合并成一条记录从数据库里面拿出来,oracle数据库。
拿到之后首先想的是,分两条sql语句去查,然后在程序里做拼接。经过沟通发现,同学的需求不是这个样子的,是要求一条sql语句获取两条记录并合并他们,数据库学的比较菜,上来就写了一个很白痴的sql

select id,
(select col from table where id=t.id and flag=1)colA,
(select col from table where id=t.id and flag=2)colA
from table t where id='123'

后续问题出来了,同学表里面并没有flag,后来想到了ROWNUM,可以通过排序后,拿到前两条记录,在做上面的查询,但是同学的table并不是单一的一张表,是又多表联合得到的,而且定位一条记录时,要通过几个条件才可以。后来在网上查询了一下,多出提到的是wmsys.wm_concat函数,但是并不符合要求,wmsys.wm_concat是将多列合并到一块,而我们需要的是分不同字段显示。最后发现了一篇帖子http://bbs.csdn.net/topics/390691977
大体思路是,先对分组对组内编号,在对组内要合并的行做操作。这里用到了max(decode(colnum,1,col2,null));
分组,组内编号

select col1,col2,row_number()over(partition by col1 order by col2)colnum from COL_TO_ROW

over: 在什么条件之上。
partition by e.deptno: 按部门编号划分(分区)。
order by e.sal desc: 按工资从高到低排序(使用rank()/dense_rank() 时,必须要带order by否则非法)
rank()/dense_rank()/row_number: 分级
整个语句的意思就是:选择col1,col2,并且按照col1的值进行分组,组内按照col2的值排列,在此基础上进行编号。
那么rank()和dense_rank()有什么区别呢?
rank(): 跳跃排序,如果有两个第一级时,接下来就是第三级。
dense_rank(): 连续排序,如果有两个第一级时,接下来仍然是第二级。
row_number():从1-n,忽略重复记录

decode函数含义解释:
decode(条件,值1,返回值1,值2,返回值2,...值n,返回值n,缺省值)

该函数的含义如下:
IF 条件=值1 THEN
    RETURN(翻译值1)
ELSIF 条件=值2 THEN
    RETURN(翻译值2)
    ......
ELSIF 条件=值n THEN
    RETURN(翻译值n)
ELSE
    RETURN(缺省值)
END IF
decode(字段或字段的运算,值1,值2,值3

因为在使用group by 时,有一个规则需要遵守,即出现在select列表中的字段,如果没有在组函数中,那么必须出现在group by 子句中。(select中的字段不可以单独出现,必须出现在group语句中或者在组函数中。)所以这里又在decode外面套了一层函数。
wmsys.wm_concat是将符合条件的列按照逗号分割将他们相加。

SQL> select id,wmsys.wm_concat(name) over (order by id) name from idtable;
ID NAME
---------- --------------------------------------------------------------------------------
10 ab,bc,cd
10 ab,bc,cd
10 ab,bc,cd
20 ab,bc,cd,hi,ij,mn
20 ab,bc,cd,hi,ij,mn
20 ab,bc,cd,hi,ij,mn

上面两个函数都要配合GROUP BY 使用。
order by 和groub by 同时出现时,order by 的栏位必须在group by 中有