笔试3G门户的时候遇到一个这样问题
<?xml:namespace prefix = o ns = "urn:schemas-microsoft-com:office:office" />
clo1
clo2
1 a
1 b
1 c
2 d
3 e
如何写SQL语句输出为:
Clo1 clo2
1 a,b,c
2 d,e
Google了一把,有点眉目特记下了,以供后用!
首先了解下oracle树型结构:
create table test (
id int ,
pid int ,
name varchar(10)) ;
id int ,
pid int ,
name varchar(10)) ;
insert into test values ( 1, 0 ,'n1' );
insert into test values ( 2, 1 ,'n12' );
insert into test values ( 3, 1 ,'n13' );
insert into test values ( 4, 2 ,'n21' );
insert into test values ( 5, 2 ,'n22' );
insert into test values ( 6, 3 ,'n31' );
insert into test values ( 7, 3 ,'n32' );
insert into test values ( 8, 4 ,'n211' );
insert into test values ( 9, 4 ,'n212' );
//从叶结点开始找根节点
// prior 关键字 与谁放在一起,就是找谁
// 从ID=7向根节点遍历
select * from test
start with id=7
connect by id = prior pid;
// 从根结点开始找子节点
从ID = 2 向叶子节点遍历
select * from test
start with id=2
connect by prior id = pid;
//如理解不了, 先把所有节点画成树状模型, 上机测试下结果就清晰了.
在理解了start with 和 connect by 子句后,再学习下row_number() over{ [partition by ] order by id } 给每一行赋予唯一的值.
例如:
Create table emp( deptno number(7,2), ename varchar2(10) );
Insert into emp values( 10, 'a' );
Insert into emp values( 10, 'b' );
Insert into emp values( 10, 'c' );
Insert into emp values( 20, 'd' );
Insert into emp values( 20, 'f' );
Insert into emp values( 30, 'g' );
Commit;
Select deptno, ename, row_number() over( order by deptno ) id
from emp;
/* 这里没用partition by */
DEPTNO ENAME ID
---------- -------------------- ----------
10 a 1
10 b 2
10 c 3
20 d 4
20 f 5
30 g 6
加上partition by 子句
Select deptno, ename, row_number() over( partition by deptno order by deptno ) id
Select deptno, ename, row_number() over( partition by deptno order by deptno ) id
from emp;
DEPTNO ENAME ID
---------- -------------------- ----------
10 a 1
10 b 2
10 c 3
20 d 1
20 f 2
30 g 1
看到差别了吧,简单.
有了前面的基础,终于可以来看最想要的语句了
.
select deptno, ltrim(max(sys_connect_by_path(ename,',')),',')
from
(select deptno, ename, row_number() over( partition by deptno order by deptno) id from emp)
start with id = 1
connect by id-1 = prior id
group by deptno;
DEPTNO LTRIM(MAX(SYS_CONNECT_BY_PATH(ENAME,',')),',')
10 g,f,c
20 g,f
30 g
问题: 加max函数用来产生聚集效果, 加ltrim( )来了去掉最左边多出的' , ', 为什么会多了个, 呢? 不太懂,还要google才行,
也可写成这样:
SELECT deptno
LTRIM(MAX(SYS_CONNECT_BY_PATH(ename,',')) KEEP (DENSE_RANK LAST ORDER BY curr),',') AS concatenated
FROM ( SELECT deptno , ename
,ROW_NUMBER() OVER (PARTITION BY deptno ORDER BY ename) AS curr
, ROW_NUMBER() OVER (PARTITION BY deptno ORDER BY ename) -1 AS prev
FROM emp )
GROUP BY deptno
CONNECT BY prev = PRIOR curr
AND deptno = PRIOR deptno
START WITH curr = 1;
转载于:https://blog.51cto.com/77857/164232