分组查询将多行字段合并到一行的SQL写法，大家看看该怎么写~~~

[weibo125043]

想使用SYS_CONNECT_BY_PATH 来实现如下效果

CREATE TABLE temp
(
DEPTNO int,
ENAME varchar(20 BYTE),
SEX varchar(20 BYTE),
AGE varchar(20 BYTE) ,
BIRTHDAY DATE
);

insert into temp values(10,'aaa','F','18',TO_DATE('2011-09-28','yyyy-mm-dd'));
insert into temp values(10,'bbb','F','19',TO_DATE('2011-09-29','yyyy-mm-dd'));
insert into temp values(10,'ccc','M','20',TO_DATE('2011-09-28','yyyy-mm-dd'));
insert into temp values(20,'ddd','M','21',TO_DATE('2011-09-28','yyyy-mm-dd'));
insert into temp values(20,'eee','M','22',TO_DATE('2011-09-28','yyyy-mm-dd'));
insert into temp values(20,'fff','M','23',TO_DATE('2011-09-28','yyyy-mm-dd'));
insert into temp values(30,'hhh','X','24',TO_DATE('2011-09-28','yyyy-mm-dd'));
insert into temp values(30,'ggg','X','25',TO_DATE('2011-09-28','yyyy-mm-dd'));
insert into temp values(30,'yyy','X','26',TO_DATE('2011-09-28','yyyy-mm-dd'));
insert into temp values(30,'ttt','X','27',TO_DATE('2011-09-28','yyyy-mm-dd'));

想达到的效果如下：按部门、性别、生日三个分组，这个组中的所有名字，在一行显示出来。
如下所示：
10 F 2011-09-28 aaa
10 F 2011-09-29 bbb
10 M 2011-09-28 ccc
20 M 2011-09-28 ddd,eee,fff
30 X 2011-09-28 hhh,ggg,yyy,ttt

SELECT deptno, sex, birthday,SYS_CONNECT_BY_PATH (ename, ',') AS ename,
FROM (SELECT deptno, ename, sex, age, RANK () OVER (ORDER BY deptno)
+ ROW_NUMBER () OVER (ORDER BY deptno) rn,
ROW_NUMBER () OVER (PARTITION BY deptno,sex,birthday ORDER BY deptno) rm
FROM temp) a1
WHERE a1.ROWID IN (SELECT MAX (a2.ROWID) FROM temp a2 WHERE a2.deptno = a1.deptno
a2.birthday=a1.birthday and a2.sex=a1.sex)
START WITH rm = 1
CONNECT BY PRIOR rn = rn - 1
但是上面的语句达不到我的效果，大家帮忙看看，哪里出错了。多谢多谢~~~

[NLP爱好者]

不知道什么结果，如果有错再改改

SELECT deptno, 
       sex, 
       birthday,
       SYS_CONNECT_BY_PATH (ename, ',') AS ename,  
  FROM 
       ( 
         SELECT deptno, 
                ename, 
                sex, 
                age,
                ROW_NUMBER () OVER (partition by deptno  ORDER BY ename, sex, age)  rn,  
                count(*) OVER (ORDER BY deptno)  rm
           FROM temp
      ) a1   
 WHERE a2.ename = a1.ename, 
       and a2.deptno   = a1.deptno  
       a2.birthday=a1.birthday 
       and 
       a2.sex=a1.sex  
       START WITH rn = 1  
       CONNECT BY PRIOR rn = rm - 1 

[weibo125043]

多谢2楼，我试了下。但是会出现这样的结果。
10 F 2011-09-28 aaa
10 F 2011-09-29 aaa,bbb
10 M 2011-09-28 ccc
20 M 2011-09-28 ddd,eee,fff
30 X 2011-09-28 hhh,ggg,yyy,ttt
第二行中会把前一条的人名给算进来。
我想根据前面三个字段分组，然后将分组得到的结果中人名那列都保存到一行的一个字段中。
目前知道根据一个字段分组是没问题的，但是想扩展到多个字段就出现了上述问题，
不知道原因出现在哪里？多谢指教。

[dawugui]

参考:

--构建测试表：

--Sql脚本:

create table test1

(

  ID   NUMBER ,

  NAME VARCHAR2(10)

);

create table test2

(

  ID   NUMBER ,

  ROLE VARCHAR2(10)

);

--测试数据

insert into test1 (ID, NAME) values (1, '张三');

insert into test1 (ID, NAME) values (2, '李四');

commit;

insert into test2 (ID, ROLE) values (1, '查询');

insert into test2 (ID, ROLE) values (1, '分析');

insert into test2 (ID, ROLE) values (1, '决策');

insert into test2 (ID, ROLE) values (2, '查询');

commit;

--要求输出结果：

      ID  NAME    ROLE

      --------------------

      1   张三       查询,分析,决策

      2   李四       查询

--方法一、使用wmsys.wm_concat

--            它是oracle在10g及其以上版本提供的一个内置函数,可以实现两字串的连接,默认是以逗号(',')进行连接

--Sql代码:

select test1.*,wmsys.wm_concat(role) role from test1,test2

where test1.id=test2.id group by test1.id,test1.NAME ;

--方法二、使用sys_connect_by_path

--            此函数也是实现字串的连接,在9i及以前就提供此函数,一般和connect by 语句结合来构造树状结构的查询

--Sql代码:

select id, name, max(ltrim(sys_connect_by_path(role, ','), ','))role

from

  (select

        row_number() over(partition by test1.id order by name) rn,

        test1.*, role

   from test1, test2  where test1.id =test2.id)

start with rn = 1

connect by prior rn = rn-1 and prior id = id

group by id, name

order by id ;

--方法三、使用自定义函数

--Sql代码

--创建function

create or replace function my_concat(p_id in number)

return varchar2

is

  result    varchar2(4000);

begin

       for rs in (select role from test2 where id=p_id) loop

           result :=result || rs.role || ',';

       end loop;

       result := rtrim(result,',');  --去掉最后一个空格

       return result;

end;

--查询sql

select test1.*,my_concat(test1.id)role from test1,test2 where test1.id=test2.id

group by test1.id,test1.name

order by test1.id ;

--拓展此函数

create or replace function my_concat2(table_name in  varchar2,   --表名

                                      p_col1     in  varchar2,   --查询条件的栏位名称

                                      p_value    in  varchar2,   --查询条件的栏位值

                                      p_col2     in  varchar2    --查询栏位，即连接栏位

)

return varchar2

IS

  type my_type is record(str varchar2(100));

  type cur is ref cursor ;

  my_cur    cur;

  rs        my_type;

  result    varchar2(4000);  --返回字符串连接后的结果集

  sql_str   varchar2(4000);  --动态查询sql语句

begin

       sql_str:='select '||p_col2||' from '||table_name||' where '||p_col1||'='||p_value;

       open my_cur for sql_str;

       loop

          fetch my_cur into rs;

                exit when my_cur%notfound;

                result :=result || rs.str || ',';

       end loop;

       result := rtrim(result,',');  --去掉最后一个空格

       return result;

end;

SELECT id,NAME, my_concat2('test2','id',id,'role')role  FROM test1;

如果是oralce 10g及以上,可利用wm_concat合并数据.