100分求一条SQL

Question

查询结果
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 

Answer 1

可在代码中进行处理，select id,nid from tablename，从返回的结果集中按nid分组组装，比直接写sql要方便。

Answer 2

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
提交完成。
类似的问题很多 ，解决的思路只有一个：构造一棵树，然后用sys_connect_by_path() 进行连接

Answer 3

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

Answer 4

sys_connect_by_path是9I才有的吧,8I怎么写

Answer 5

哥们，表转置是需要PLSQL来完成的。一般的SQL好像做不到的！

Answer 6

sys_connect_by_path是9I才有的吧,8I怎么写
=========
8I里需要自己写一个类似sys_connect_by_path的函数来完成了。这个函数网上有。
参考一下吧
http://www.52sdn.com/artid/120/120151.html