sql

求一个sql

SQL code

表 mytable  a_id      p_id  1           2       1           3      2           1      3           1      4           1     1           4  5           2我想找出每一个p_id对应的所有的a_id数据，然后我想取到一个合并的结果集p_id 为1的a_id 有三个 2 3 4p_id 为2的有两个  1 5p_id为3的有一个1p_id 为4的有一个1这种表有办法完成么？谢谢。

SQL code

select a_id,replace(wm_concat(a_id),',','') result from table;
[解决办法]
SQL code
--9i,10g可用with mytable as(  select 1 a_id,2 p_id from dual union all    select 1 a_id,3 p_id from dual union all   select 2 a_id,1 p_id from dual union all   select 3 a_id,1 p_id from dual union all   select 4 a_id,1 p_id from dual union all  select 1 a_id,4 p_id from dual union all  select 5 a_id,2 p_id from dual)SELECT a.p_id, ltrim(MAX(sys_connect_by_path(a_id, ',')), ',') a_id  FROM (SELECT row_number() over(PARTITION BY t.p_id ORDER BY t.p_id) rn, t.* FROM mytable t) a START WITH rn = 1CONNECT BY PRIOR rn = rn - 1 AND           a.p_id = PRIOR a.p_id GROUP BY p_id ORDER BY p_id;
[解决办法]
SQL code
--10g用select p_id,wm_concat(a_id) result from table group by p_id;
[解决办法]
SQL code
测试数据:select * from a;        ID SNAME---------- --------------------------------------------------          2 yang         4 Riddd         2 ff         4 Hlia         4 YreerSYS_CONNECT_BY_PATH适用范围：8i,9i,10g及以后版本SELECT t.id id, MAX(substr(sys_connect_by_path(t.sname, ','), 2)) strFROM (SELECT id, sname, row_number() over(PARTITION BY id ORDER BY sname) rnFROM a) tSTART WITH rn = 1CONNECT BY rn = PRIOR rn + 1AND id = PRIOR idGROUP BY t.id;