今天下午帮助客服后台刷数据库,大概有A 表 43万的数据量, B 表大概有1万条数据。
功能是:需要将B表汇总后的数据同步到A表。
第一次使用语句用如下:
UPDATE B SET B.sumtdxx=(SELECT SUM(mj) FROM t WHERE B.OBJECTID=T.OBJECTID AND T.ISEFFECT=0)
WHERE exists (select * from t where t.objectid=b.objectid and t.iseffect=0 and b.iseffect=0)
运行了10几分钟没见结果,想想,有4条这样的语句,那不搞上好几个小时,当时客户也催的急。
第二次更换成
MERGE INTO B
USING T
ON (B.OBJECTID=T.OBJECTID)
WHEN MATCHED THEN
UPDATE SET B.sumtdxxht=(SELECT SUM(mj) FROM t
WHERE B.OBJECTID=T.OBJECTID AND T.ISEFFECT=0 and t.tdlb=2)
效果还是不理想。执行了3分钟没见结果。
第三次:使用临时表+MERGE 的方式:10秒搞定。
create table T_sum as select
sum(case when s1.tdlb = '1' and s1.mj is not null then s1.mj else 0 end) as SUMTDXXST,
sum(case when s1.tdlb = '2' and s1.mj is not null then s1.mj else 0 end) as SUMTDXXHT,
sum(case when s1.tdlb = '3' and s1.mj is not null then s1.mj else 0 end) as SUMTDXXCD,
sum(case when s1.tdlb = '4' and s1.mj is not null then s1.mj else 0 end) as SUMTDXXELSE,
s1.objectid from T_32e5bcg7 s1 WHERE S1.ISEFFECT =0 group by s1.objectid;
commit;
MERGE INTO b
USING T_sum s
ON (b.OBJECTID=s.OBJECTID )
WHEN MATCHED THEN
UPDATE SET b.SUMTDXXST = s.SUMTDXXST,
b.SUMTDXXHT = s.SUMTDXXHT,
b.SUMTDXXCD = s.SUMTDXXCD,
b.SUMTDXXELSE = s.SUMTDXXELSE;
update b set b.SUMTDXX=(b.SUMTDXXST+ b.SUMTDXXHT+b.SUMTDXXCD+b.SUMTDXXELSE);
commit;
drop table T_sum;