我这里有2张表 a(refundId,verifyCodes,orderId)其中refundId是主键
如数据格式('0001','00011,00022,00033,','01')
b(id,verifyCode,orderId)其中id是主键,这个表中,
如数据格式
('00001','00011','01')
('00002','00022','01')
('00003','00033','01')
('00004','00051','02')
('00005','00052','02')
('00006','00053','02')
('00007','00054','02')
我现在希望通过orderId这个字段把这两张表关联起来
把b表中的verifyCode字段,组成date1,date2,date3,的样式,然后通过orderId,来update表a
让a表中字段verifyCodes中的内容为00011,00022,00033,形式
请求帮助,谢谢
如数据格式('0001','00011,00022,00033,','01')
b(id,verifyCode,orderId)其中id是主键,这个表中,
如数据格式
('00001','00011','01')
('00002','00022','01')
('00003','00033','01')
('00004','00051','02')
('00005','00052','02')
('00006','00053','02')
('00007','00054','02')
我现在希望通过orderId这个字段把这两张表关联起来
把b表中的verifyCode字段,组成date1,date2,date3,的样式,然后通过orderId,来update表a
让a表中字段verifyCodes中的内容为00011,00022,00033,形式
请求帮助,谢谢
SET a.verifycodes =
(SELECT verifycodes
FROM (SELECT orderid, wm_concat(b.verifycode) verifycodes
FROM b
GROUP BY b.orderid) c
WHERE c.orderid = a.orderid)
WHERE EXISTS (SELECT 1 FROM b WHERE b.orderid = a.orderid);
create table a(
refountid varchar2(6) primary key,
verifycodes varchar2(30),
orderid varchar2(3)
)
--
create table b(
id varchar2(7) primary key,
verifycode varchar2(7),
orderid varchar2(3)
)
SQL> select * from a;REFOUNTID VERIFYCODES ORDERID
--------- ------------------------------ -------
0001 01
0002 02SQL> select * from b;ID VERIFYCODE ORDERID
------- ---------- -------
00001 00011 01
00002 00022 01
00003 00033 01
00004 00051 02
00005 00052 02
00006 00053 02
00007 00054 027 rows selectedSQL>
SQL> update a
2 set a.verifycodes=(
3 select c.code
4 from (
5 select orderid,wm_concat(verifycode) code
6 from b
7 group by orderid)c
8 where a.orderid=c.orderid)
9 where exists(
10 select 1
11 from b
12 where a.orderid=b.orderid)
13 /2 rows updatedSQL> select * from a;REFOUNTID VERIFYCODES ORDERID
--------- ------------------------------ -------
0001 00011,00022,00033 01
0002 00051,00052,00053,00054 02
,用WMSYS.WM_CONCAT函数SELECT ORDERID, WMSYS.WM_CONCAT(VERIFYCODE) FROM B GROUP BY ORDERID;
using (select orderid, wm_concat(b.verifycode) w from b group by orderid) t2
on (t1.orderid = t2.orderid)
when matched then
update t1.verifycode = t2.w;