--不知是否要这个 select a_id,p_id from mytable where p_id in (1,2,3,4);
10g或以上,用:select a_id,replace(wm_concat(a_id),',',' ') from my_able;
p_id会很大,呵呵,不只是1 2 3 4
Error at line 1 ORA-00937: 不是单组分组函数
select a_id,replace(wm_concat(a_id),',','') result from table;
--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 = 1 CONNECT BY PRIOR rn = rn - 1 AND a.p_id = PRIOR a.p_id GROUP BY p_id ORDER BY p_id;
--10g用 select p_id,wm_concat(a_id) result from table group by p_id;
测试数据: select * from a; ID SNAME ---------- -------------------------------------------------- 2 yang 4 Riddd 2 ff 4 Hlia 4 Yreer SYS_CONNECT_BY_PATH 适用范围:8i,9i,10g及以后版本 SELECT t.id id, MAX(substr(sys_connect_by_path(t.sname, ','), 2)) str FROM (SELECT id, sname, row_number() over(PARTITION BY id ORDER BY sname) rn FROM a) t START WITH rn = 1 CONNECT BY rn = PRIOR rn + 1 AND id = PRIOR id GROUP BY t.id;
select a_id,p_id from mytable where p_id in (1,2,3,4);
ORA-00937: 不是单组分组函数
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 = 1
CONNECT BY PRIOR rn = rn - 1 AND
a.p_id = PRIOR a.p_id
GROUP BY p_id
ORDER BY p_id;
select p_id,wm_concat(a_id) result from table group by p_id;
select * from a;
ID SNAME
---------- --------------------------------------------------
2 yang
4 Riddd
2 ff
4 Hlia
4 Yreer
SYS_CONNECT_BY_PATH
适用范围:8i,9i,10g及以后版本
SELECT t.id id, MAX(substr(sys_connect_by_path(t.sname, ','), 2)) str
FROM (SELECT id, sname, row_number() over(PARTITION BY id ORDER BY sname) rn
FROM a) t
START WITH rn = 1
CONNECT BY rn = PRIOR rn + 1
AND id = PRIOR id
GROUP BY t.id;