合并sql数据

create table BILL
(
BILL_ID NUMBER not null, --主键
CHARGE NUMBER, --钱
PAY_ID NUMBER --外键，关联PAYMENT表的PAY_ID字段
)

insert into bill(bill_id,charge,pay_id) values(1,10,1);
insert into bill(bill_id,charge,pay_id) values(2,10,1);
insert into bill(bill_id,charge,pay_id) values(3,30,2);
insert into bill(bill_id,charge,pay_id) values(4,40,2);


create table PAYMENT
(
PAY_ID NUMBER not null, --主键（主表）
CHARGE NUMBER --钱
)


insert into payment(pay_id,charge) values (1,0);
insert into payment(pay_id,charge) values (2,0);

合并后：
payment表数据：
pay_id charge
1 20
2 70

MERGE INTO payment T1
USING (select pay_id, sum(charge) as charge from bill group by pay_id) T2
ON (T1.pay_id = T2.pay_id)
WHEN MATCHED THEN
UPDATE SET T1.charge = T2.charge
WHEN NOT MATCHED THEN
INSERT (pay_id, charge) VALUES (T2.pay_id, T2.charge);