表 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 4
p_id 为2的有两个 1 5
p_id为3的有一个1
p_id 为4的有一个1
这种表有办法完成么?谢谢。
解决方案 »
- linux下 sqlldr导入问题
- 如何在命令提示符下连接ORACLE数据库?
- 如何判断字段是不是中文
- select df,fdf,sdf, from tabe1 a,tale2 b where b.date>=sysdate-10 and a.date(+)>=sysdate-10
- 热盼:oracle同步解决方案
- 如何利用 PL/sql developer 从服务器导出表结构!
- redhat9 安装ORACLE8I 求binutils-2.10.91.0.2-3.i386.rpm下载地址
- 在Oracle9中如何增加temp表空间?
- 两个问题?
- 类似 sql server 语句中的 if exists ...(如果存在就...)用法在oracle中怎么用?
- shutdown immediate后startup 报错
- 软解析和硬解析的问题
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;