求一个sql语句的些法

[jywbd]

我这里有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，形式
请求帮助，谢谢

[tangren]

UPDATE a
   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);

[304的的哥]

[Quote=引用 1 楼 tangren 的回复:]

SQL code
UPDATE a
SET a.verifycodes =
(SELECT verifycodes
FROM (SELECT orderid, wm_concat(b.verifycode) verifycodes
FROM b
GROUP BY b.orderid) ……
[/Quote]
高手就是不一样!

[304的的哥]

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                                     02

SQL> 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      02

7 rows selected

SQL> 
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 updated

SQL> select * from a;

REFOUNTID VERIFYCODES                    ORDERID
--------- ------------------------------ -------
0001      00011,00022,00033              01
0002      00051,00052,00053,00054        02

[yinan9]

主要还是行列转换的问题
，用WMSYS.WM_CONCAT函数

SELECT ORDERID, WMSYS.WM_CONCAT(VERIFYCODE) FROM B GROUP BY ORDERID;

[yinan9]

[Quote=引用 3 楼 bobo12082119 的回复:]
SQL code

create table a(
refountid varchar2(6) primary key,
verifycodes varchar2(30),
orderid varchar2(3)
)
--
create table b(
id varchar2(7) primary key,
……
[/Quote]学友兄，你太给力了

[ssqtjffcu]

merge into a t1
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;