想使用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,tttSELECT 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
但是上面的语句达不到我的效果,大家帮忙看看,哪里出错了。多谢多谢~~~
(
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,tttSELECT 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
但是上面的语句达不到我的效果,大家帮忙看看,哪里出错了。多谢多谢~~~
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
--构建测试表:--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,test2where 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, ','), ','))rolefrom (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 = 1connect by prior rn = rn-1 and prior id = idgroup by id, nameorder by id ;--方法三、使用自定义函数--Sql代码--创建functioncreate or replace function my_concat(p_id in number)return varchar2is 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;--查询sqlselect test1.*,my_concat(test1.id)role from test1,test2 where test1.id=test2.idgroup by test1.id,test1.nameorder 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 varchar2IS 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合并数据.
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
第二行中会把前一条的人名给算进来。
我想根据前面三个字段分组,然后将分组得到的结果中人名那列都保存到一行的一个字段中。
目前知道根据一个字段分组是没问题的,但是想扩展到多个字段就出现了上述问题,
不知道原因出现在哪里?多谢指教。