一个常见的查询

在csdn看经常看到类似这样的帖子：

查询结果
id                       nid
1000000318    2                                                                                                                              
1000000644    2                                                                                                                              
1000000927    2                                                                                                                              
1000000319    3                                                                                                                              
1000000645    3                                                                                                                              
1000000928    3                                                                                                                              
变成这个结果
id nid
1000000318 ,1000000644 ,1000000927  2
1000000319 ,1000000645 ,1000000928  3
怎么写SQL  

类似的问题很多 ，解决的思路只有一个：构造一棵树，然后用sys_connect_by_path() 进行连接

SQL> create table test(id number,nid number);

表已创建。

SQL> insert into test values(1000000318,2 );

已创建 1 行。

SQL> insert into test values(1000000644,2 );

已创建 1 行。

SQL> insert into test values(1000000927,2 );

已创建 1 行。

SQL> insert into test values(1000000319,3 );

已创建 1 行。

SQL> insert into test values(1000000645,3 );

已创建 1 行。

SQL> insert into test values(1000000928,3 );

已创建 1 行。

SQL> commit;
SQL> column max(resu) format a50
SQL> column max(resu) format a50
SQL> select max(resu), nid
  2    from (select substr(sys_connect_by_path(id, ','), 2) resu, nid
  3            from (select t.*, lead(rn) over(partition by nid order by rn) prn

  4                    from (select rownum rn, id, nid from test) t)
  5          connect by prior rn = prn
  6           start with prn is null)
  7   group by nid;

MAX(RESU)                                                 NID
-------------------------------------------------- ----------
1000000927,1000000644,1000000318                            2
1000000928,1000000645,1000000319                            3
提交完成。

 

 至于连接的顺序，在于你怎样构建这颗树，比如 例子中order by rn 改为order by rn desc 则结果变为

1 1000000318,1000000644,1000000927  2
2 1000000319,1000000645,1000000928  3