Question

db2查询sql如何写

RT，假如我有2张表，一张表a有字段order_id和merchant_id，另外一张表b有refund_id,order_id，2张表用order_id关联，现在我b表里可能有2条order_id一样的记录（order_id是a表的主键），我要怎样写sql能查出一条a表的记录，同时把b表对应order_id的2条记录的refund_id用|拼接起来。
例如a表记录：123123 88888888，b表记录 1 123123，2 123123
我要查出 123123 88888888 1|2

Answer 1

这样行吗 不过不是用 |  连的 用竖线比较复杂还得连接

 

替换成你表中的信息就行 你参考一下

SELECT T1.order_id,T1.merchant_id,wm_concat(T2.refund_id)
FROM A T1,B T2
WHERE T1.order_id = T2.order_id

group by T1.order_id,T1.merchant_id

Answer 2

with s1(f1, f2, rn) as (
select order_id, trim(char(refund_id)),
row_number() over(partition by order_id order by refund_id)
from b
),
s2(f1, f2, rn) as (
select f1, cast(f2 as varchar(1000)), rn from s1 where rn=1
union all
select s1.f1, cast(s2.f2||'|'||s1.f2 as varchar(1000)), s1.rn
from s1,s2
where s1.rn=s2.rn+1 and s1.f1=s2.f1
),
s3(f1,f2,rn) as (
select * from s2
where (f1, rn) in (select f1, max(rn) from s2 group by f1)
)
select a.*, s3.f2
from a left join s3
on a.order_id=s3.f1

Answer 3

和其它数据库的写法大致一样，就是自然连接