笔试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)) ;

　　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

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;