Fkey ID
1 1
1 2
2 3
2 4
3 5
ID name other
1 方案1 ssss
1 方案2 ddddd
1 方案3 ffff
2 方案1 aaa
2 方案2 ss
3 方案1 ff
3 方案2 ee
4 方案1 rr
5 方案1 ss
想得到 以下 数据
Fkey ID name other
1 1 方案1；方案2；方案3 ssss；ddddd；ffff
1 2 方案1；方案2 aaa；ss
2 3 方案1；方案2 ff；ee
2 4 方案1 rr
3 5 方案1 ss
7 个解决方案
#1
select a.Fkey,
b.id,
listagg(b.name,';') withon group (order by b.name) name,
listagg(b.other,';') withon group (order by b.name) other
from a,b
where a.id=b.id
group by a.Fkey,b.id;
#2
select a.Fkey,
b.id,
listagg(b.name,';') within group (order by b.name) name,
listagg(b.other,';') within group (order by b.name) other
from a,b
where a.id=b.id
group by a.Fkey,b.id;
笔误
，是within不是withon
#3
SQL>
SQL> col fkey format a3;
SQL> col id format a3;
SQL> create table a(Fkey int, ID int);
Table created
SQL> create table b(ID int, name varchar(10), other varchar(10));
Table created
SQL> begin
2 insert into a values(1, 1);
3 insert into a values(1, 2);
4 insert into a values(2, 3);
5 insert into a values(2, 4);
6 insert into a values(3, 5);
7
8 insert into b values(1, '方案1', 'ssss');
9 insert into b values(1, '方案2', 'ddddd');
10 insert into b values(1, '方案3', 'ffff');
11 insert into b values(2, '方案1', 'aaa');
12 insert into b values(2, '方案2', 'ss');
13 insert into b values(3, '方案1', 'ff');
14 insert into b values(3, '方案2', 'ee');
15 insert into b values(4, '方案1', 'rr');
16 insert into b values(5, '方案1', 'ss');
17 end;
18 /
PL/SQL procedure successfully completed
SQL> select a.fkey, a.id ,
2 listagg(b.name,',') within group (order by name) c1,
3 listagg(b.other,',') within group (order by other) c2
4 from a, b where a.id = b.id
5 group by a.fkey, a.id;
FKE ID C1 C2
--- --- ------------------------------- ---------------------
1 1 方案1,方案2,方案3 ddddd,ffff,ssss
1 2 方案1,方案2 aaa,ss
2 3 方案1,方案2 ee,ff
2 4 方案1 rr
3 5 方案1 ss
SQL> drop table a purge;
Table dropped
SQL> drop table b purge;
Table dropped
SQL>
#4
熊猫神速啊。
#5
10G的话，可以用wm_concat
#6
楼上写的都对，鼓掌
#7
补充
SELECT
A.FKEY, B.ID,
LISTAGG(B.NAME, ';') WITHIN GROUP (ORDER BY B.NAME) NAME,
LISTAGG(B.OTHER, ';') WITHIN GROUP (ORDER BY B.OTHER DESC) OTHER
FROM A, B
WHERE A.ID = B.ID
GROUP BY B.ID,A.FKEY;
#1
select a.Fkey,
b.id,
listagg(b.name,';') withon group (order by b.name) name,
listagg(b.other,';') withon group (order by b.name) other
from a,b
where a.id=b.id
group by a.Fkey,b.id;
#2
select a.Fkey,
b.id,
listagg(b.name,';') within group (order by b.name) name,
listagg(b.other,';') within group (order by b.name) other
from a,b
where a.id=b.id
group by a.Fkey,b.id;
笔误
，是within不是withon
#3
SQL>
SQL> col fkey format a3;
SQL> col id format a3;
SQL> create table a(Fkey int, ID int);
Table created
SQL> create table b(ID int, name varchar(10), other varchar(10));
Table created
SQL> begin
2 insert into a values(1, 1);
3 insert into a values(1, 2);
4 insert into a values(2, 3);
5 insert into a values(2, 4);
6 insert into a values(3, 5);
7
8 insert into b values(1, '方案1', 'ssss');
9 insert into b values(1, '方案2', 'ddddd');
10 insert into b values(1, '方案3', 'ffff');
11 insert into b values(2, '方案1', 'aaa');
12 insert into b values(2, '方案2', 'ss');
13 insert into b values(3, '方案1', 'ff');
14 insert into b values(3, '方案2', 'ee');
15 insert into b values(4, '方案1', 'rr');
16 insert into b values(5, '方案1', 'ss');
17 end;
18 /
PL/SQL procedure successfully completed
SQL> select a.fkey, a.id ,
2 listagg(b.name,',') within group (order by name) c1,
3 listagg(b.other,',') within group (order by other) c2
4 from a, b where a.id = b.id
5 group by a.fkey, a.id;
FKE ID C1 C2
--- --- ------------------------------- ---------------------
1 1 方案1,方案2,方案3 ddddd,ffff,ssss
1 2 方案1,方案2 aaa,ss
2 3 方案1,方案2 ee,ff
2 4 方案1 rr
3 5 方案1 ss
SQL> drop table a purge;
Table dropped
SQL> drop table b purge;
Table dropped
SQL>
#4
熊猫神速啊。
#5
10G的话，可以用wm_concat
#6
楼上写的都对，鼓掌
#7
补充
SELECT
A.FKEY, B.ID,
LISTAGG(B.NAME, ';') WITHIN GROUP (ORDER BY B.NAME) NAME,
LISTAGG(B.OTHER, ';') WITHIN GROUP (ORDER BY B.OTHER DESC) OTHER
FROM A, B
WHERE A.ID = B.ID
GROUP BY B.ID,A.FKEY;