合并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);