[转]将查询结果中同一字段多行合并到一行下的SQL语句写法

[转]将查询结果中同一字段多行合并到一行上的SQL语句写法

在开发过程中经常碰到要实现按照某一字段，将其它字段中不同的值连接后显示到一行上的需求。本人总结了以下几种方法，在实际开发中可以参考。

建立表结构，在Oracle9i上。
CREATE TABLE temp
(
DEPTNO?? int,
ENAME?? varchar(20 BYTE),
SEX?? varchar(20 BYTE),
AGE?? varchar(20 BYTE)
);

insert into temp values(10,'aaa','F','18');
insert into temp values(10,'bbb','F','19');
insert into temp values(10,'ccc','F','20');
insert into temp values(20,'ddd','M','21');
insert into temp values(20,'eee','M','22');
insert into temp values(20,'fff','M','23');
insert into temp values(30,'hhh','X','24');
insert into temp values(30,'ggg','X','25');
insert into temp values(30,'yyy','X','26');
insert into temp values(30,'ttt','X','27');

commit;

第一种写法：

SELECT DISTINCT FIRST_VALUE (deptno) OVER (PARTITION BY deptno ORDER BY lv DESC) AS deptno,
??????????????? FIRST_VALUE (ename) OVER (PARTITION BY deptno ORDER BY lv DESC) AS ename
?????????? FROM (SELECT deptno, SYS_CONNECT_BY_PATH (ename, ' ') ename,
??????????????????????????? LEVEL lv FROM (SELECT deptno, ename,
??????????????????????????????????? LAG (ename, 1, NULL) OVER (PARTITION BY deptno ORDER BY ename) ename_1
?????????????????????????????? FROM (SELECT deptno, ename FROM temp))
???????????????? CONNECT BY PRIOR ename = ename_1
?????????????????? ORDER BY deptno)

结果如下：

10| aaa bbb ccc
20| ddd eee fff
30| ggg hhh ttt yyy

改进后可以增加一列如下：

SELECT DISTINCT FIRST_VALUE (deptno) OVER (PARTITION BY deptno ORDER BY lv DESC) AS deptno,
??????????????? FIRST_VALUE (sex) OVER (PARTITION BY sex ORDER BY lv DESC) AS sex,
??????????????? FIRST_VALUE (ename) OVER (PARTITION BY deptno ORDER BY lv DESC) AS ename
?????????? FROM (SELECT deptno, sex, SYS_CONNECT_BY_PATH (ename, ' ') ename, LEVEL lv
?????????????????????? FROM (SELECT deptno, ename, sex,
??????????????????????????????????? LAG (ename, 1, NULL) OVER (PARTITION BY deptno ORDER BY ename) ename_1
?????????????????????????????? FROM (SELECT deptno, ename, sex FROM temp))
???????????????? CONNECT BY PRIOR ename = ename_1
?????????????????? ORDER BY deptno)

结果如下：
10|F| aaa bbb ccc
20|M| ddd eee fff
30|X| ggg hhh ttt yyy

第二种写法：

SELECT deptno, SYS_CONNECT_BY_PATH (ename, ' ') AS ename, sex,
?????????? SYS_CONNECT_BY_PATH (age, ' ') AS age
????? FROM (SELECT deptno, ename, sex, age, RANK () OVER (ORDER BY deptno)
?????????????????? + ROW_NUMBER () OVER (ORDER BY deptno) rn,
?????????????????? ROW_NUMBER () OVER (PARTITION BY deptno ORDER BY deptno) rm
????????????? FROM temp) a1
???? WHERE a1.ROWID IN (SELECT MAX (a2.ROWID) FROM temp a2 WHERE a2.deptno = a1.deptno)
START WITH rm = 1
CONNECT BY PRIOR rn = rn - 1

结果如下：
10| aaa bbb ccc|F| 18 19 20
20| ddd eee fff|M| 21 22 23
30| hhh ggg yyy ttt|X| 24 25 26 27

?

出自：http://hi.baidu.com/rel_conquer/blog/item/fb3bb6f3a7a5185d352acc08.html

?

?

?

Oracle中SYS_CONNECT_BY_PATH函数是非常重要的函数，下面就为您介绍一个使用SYS_CONNECT_BY_PATH函数的例子，实例如下：

create?table?test?(a?varchar2(10),b?varchar2(10)); ??
INSERT?INTO?TEST?(A,?B)?VALUES?('1',?'我'); ?INSERT?INTO?TEST?(A,?B)?VALUES?('1',?'们'); ?
INSERT?INTO?TEST?(A,?B)?VALUES?('2',?'一'); ?INSERT?INTO?TEST?(A,?B)?VALUES?('2',?'起'); ?
COMMIT; ??
SELECT?A,?B?FROM?TEST ??
A??????????B ?----------?---------- ?
1??????????我 ?1??????????们 ?
2??????????一 ?2??????????起 ?
?现在需要达到如下的效果， ?
A??????????B ?----------?---------- ?
1??????????我,们 ?2??????????一,起 ?
?

只想用一句sql来返回结果。

SELECT?A,?LTRIM(MAX(SYS_CONNECT_BY_PATH(B,?',')),?',')?B ?FROM?(SELECT?B,?A,?ROW_NUMBER()?OVER(PARTITION?BY?A?ORDER?BY?B?DESC)?RN ?
??????????FROM?TEST) ?START?WITH?RN?=?1?
CONNECT?BY?RN?-?1?=?PRIOR?RN ????????AND?A?=?PRIOR?A ?
GROUP?BY?A;?

其中，SYS_CONNECT_BY_PATH函数主要作用是可以把一个父节点下的所有子节点通过某个字符进行区分，然后连接在一个列中显示。

row_number函数的用途是非常广泛，这个函数的功能是为查询出来的每一行记录生成一个序号。生产序号的方法通过over()函数里面的语句来控制。