[转]将查询结果中同一字段多行合并到一行上的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函数的例子，实例如下：

1. create table test (a varchar2(10),b varchar2(10));  
2.  
3. INSERT INTO TEST (A, B) VALUES ('1', '我');  
4. INSERT INTO TEST (A, B) VALUES ('1', '们');  
5. INSERT INTO TEST (A, B) VALUES ('2', '一');  
6. INSERT INTO TEST (A, B) VALUES ('2', '起');  
7. COMMIT;  
8.  
9. SELECT A, B FROM TEST  
10.  
11. A          B  
12. ---------- ----------  
13. 1          我  
14. 1          们  
15. 2          一  
16. 2          起  
17.  
18. 现在需要达到如下的效果，  
19. A          B  
20. ---------- ----------  
21. 1          我,们  
22. 2          一,起  
23.  

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

1. SELECT A, LTRIM(MAX(SYS_CONNECT_BY_PATH(B, ',')), ',') B  
2. FROM (SELECT B, A, ROW_NUMBER() OVER(PARTITION BY A ORDER BY B DESC) RN  
3.           FROM TEST)  
4. START WITH  RN  =  1  
5. CONNECT BY RN -  1  =  PRIOR  RN  
6.        AND  A  =  PRIOR  A  
7. GROUP BY A; 

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

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