db2语句编写，急求

显示全部楼层 · 2009-7-22 09:30:00

有主子表2张，主表与子表是一对多的关系，现在希望把子表的关联的信息合并，比如A表
a列
1
2
b表
111
112
113
221
222
希望结果是
1 11，12，13
2 21，22

谢谢

千问 · 2009-7-22 09:30:00

可以用递归实现：
with table1 ( c1 ) as (
values (1),(2) )
,table2 ( c1,c2 ) as (
values(1,11),
(1,12),
(1,13),
(2,11),
(2,12) )
, temp1 ( c1,c2,c3 ) as ( select t1.c1,trim(char(t1.c2)),row_number() over ( partition by t1.c1 order by t1.c2 )
from table1 t2, table2 t1 where t1.c1=t2.c1 )
, temp2 ( c1 ,c2 ,c3 ) as (
select c1,cast(c2 as varchar(20)),c3 from temp1 where c3 = 1
union all
select a.c1,concat(concat(b.c2,','),a.c2),a.c3 from temp1 a, temp2 b
where a.c1 = b.c1 and a.c3 = b.c3 + 1 )
select c1,c2 from temp2 where (c1,c3) in ( select c1,max(c3) from temp2 group by c1 )

千问 · 2009-7-22 09:30:00

我找到了其他方法
select tt.id ,left(tt.jydy_str,length(tt.jydy_str)-1) from (
SELECT d.ID,
rtrim(replace(replace(xml2clob(xmlagg(xmlelement(name a, s.JYDY_ID||','))),'',''),'',' ')) jydy_str
FROMd,s
where d.id = s.CLIENT_DEAL_ID
group by d.ID) tt;

千问 · 2009-7-22 09:30:00

create table t_main(id int not null, name varchar(32));
create table t_sub(pid int not null, val varchar(32));
insert into t_main values(1,'a');
insert into t_main values(2,'b');
insert into t_main values(3,'c');
insert into t_sub values(1,'11');
insert into t_sub values(1,'12');
insert into t_sub values(1,'13');
insert into t_sub values(2,'21');
insert into t_sub values(2,'22');
insert into t_sub values(2,'23');
commit;
select id, name, (
SELECT substr(replace(replace(xmlserialize(xmlagg(xmlelement(NAME a, val)) as varchar(2000)),'',','),'',''), 2)
FROM t_sub
where pid = id
GROUP BY pid
)
from t_main;
select id, name, (
select substr(listagg(',' ||val), 2)
FROM t_sub
where pid = id
)
from t_main;

千问 · 2009-7-22 09:30:00

listagg最简单，不过好像是9.7.4（9.7.3?）才有的

千问 · 2009-7-22 09:30:00

还有这么好的函数，我记得原来写老麻烦了，效率还不高，贴个我们原来的。
select * from STRTES
ID
STR
----------- ----------------
1 a
1 b
2 b
1 c

规则: 按ID进行分组，进行STR的合并
with s(rid1,rid2,id,str)
as (

select row_number() over(partition by id order by str) rid1,

row_number() over(partition by id order by str) rid1,

ID,

STRfrom STRTES

)
,
t(rid1,rid2,id,str)
as (

select rid1,rid2,id,trim(str) from s where rid1=1 and rid2=1

union all

select t.rid1,t.rid2+1,t.id,cast(trim(t.str)||','||trim(s.str) as varchar(100))

from s,t

where s.rid1=t.rid2+1 and s.id=t.id

)
select t.id,t.str from t,(select max(rid1) rid,id from s group by id) tt where t.id=tt.id and t.rid2=tt.rid order by t.id

ID
STR
----------- -----------------
1
a,b,c
2
b

千问 · 2009-7-22 09:30:00

zcl32 发表于 2012-1-9 17:09
listagg最简单，不过好像是9.7.4（9.7.3?）才有的
是的，9.7.4