两表数据结构一致,都没有主键。这样查速度很慢,哪位可以提供一个好的办法。急!!!
UPDATE MAIN_PAS X SET AMOUNT=X.AMOUNT+
(SELECT SUM(AMOUNT) AMOUNT FROM temp_import b WHERE b.VEHICLECD=x.VEHICLECD and b.MODELCD=x.MODELCD and b.BRANDCD=x.BRANDCD
and b.DISBRANDCD=x.DISBRANDCD and b.MAKERCD=x.MAKERCD and b.COLORCD=x.COLORCD and b.DISMAKERCD=x.DISMAKERCD and b.GROUPCD=x.GROUPCD and b.OLDSEGMENT=x.OLDSEGMENT and b.CLASS1CD=x.CLASS1CD
and b.CLASS2CD=x.CLASS2CD and b.CLASS3=x.CLASS3CD and b.BODYTYPE=x.BODYTYPE and b.DOMECBUCD=x.DOMECBUCD and b.PROVINCECD=x.PROVINCECD and b.CITYCD=x.CITYCD and
b.DISTRICTCD =x.DISTRICTCD and b.POSTALCODE=x.POSTALCODE and b.REGYEARMONTH=x.REGYEARMONTH and b.USAGECD=x.USAGECD and b.ACQUISITIONCD=x.ACQUISITIONCD and b. VIN =x. VIN and b.OWNERSHIPCD=x.OWNERSHIPCD
and b.COUNTRYCD=x.COUNTRYCD and b.DISCOUNTRYCD and b.ENGINE=x.ENGINE and b.FUELCD=x.FUELCD and b.DISPLACEMENT=x.DISPLACEMENT and b.TRANSMISSIONCD=x.TRANSMISSIONCD and b.GEARCD=x.GEARCD and b. PRICE=x.PRICE
and b.OWNERYEAR=x.OWNERYEAR
UPDATE MAIN_PAS X SET AMOUNT=X.AMOUNT+
(SELECT SUM(AMOUNT) AMOUNT FROM temp_import b WHERE b.VEHICLECD=x.VEHICLECD and b.MODELCD=x.MODELCD and b.BRANDCD=x.BRANDCD
and b.DISBRANDCD=x.DISBRANDCD and b.MAKERCD=x.MAKERCD and b.COLORCD=x.COLORCD and b.DISMAKERCD=x.DISMAKERCD and b.GROUPCD=x.GROUPCD and b.OLDSEGMENT=x.OLDSEGMENT and b.CLASS1CD=x.CLASS1CD
and b.CLASS2CD=x.CLASS2CD and b.CLASS3=x.CLASS3CD and b.BODYTYPE=x.BODYTYPE and b.DOMECBUCD=x.DOMECBUCD and b.PROVINCECD=x.PROVINCECD and b.CITYCD=x.CITYCD and
b.DISTRICTCD =x.DISTRICTCD and b.POSTALCODE=x.POSTALCODE and b.REGYEARMONTH=x.REGYEARMONTH and b.USAGECD=x.USAGECD and b.ACQUISITIONCD=x.ACQUISITIONCD and b. VIN =x. VIN and b.OWNERSHIPCD=x.OWNERSHIPCD
and b.COUNTRYCD=x.COUNTRYCD and b.DISCOUNTRYCD and b.ENGINE=x.ENGINE and b.FUELCD=x.FUELCD and b.DISPLACEMENT=x.DISPLACEMENT and b.TRANSMISSIONCD=x.TRANSMISSIONCD and b.GEARCD=x.GEARCD and b. PRICE=x.PRICE
and b.OWNERYEAR=x.OWNERYEAR
from X,(select VEHICLECD,MODELCD,BRANDCD,DISBRANDCD,.....,AMOUNT =sum(AMOUNT) from temp_import group by VEHICLECD,MODELCD,BRANDCD,DISBRANDCD,.....)b
where b.VEHICLECD=x.VEHICLECD and b.MODELCD=x.MODELCD and b.BRANDCD=x.BRANDCD
and b.DISBRANDCD=x.DISBRANDCD and b.MAKERCD=x.MAKERCD and b.COLORCD=x.COLORCD and b.DISMAKERCD=x.DISMAKERCD and b.GROUPCD=x.GROUPCD and b.OLDSEGMENT=x.OLDSEGMENT and b.CLASS1CD=x.CLASS1CD
and b.CLASS2CD=x.CLASS2CD and b.CLASS3=x.CLASS3CD and b.BODYTYPE=x.BODYTYPE and b.DOMECBUCD=x.DOMECBUCD and b.PROVINCECD=x.PROVINCECD and b.CITYCD=x.CITYCD and
b.DISTRICTCD =x.DISTRICTCD and b.POSTALCODE=x.POSTALCODE and b.REGYEARMONTH=x.REGYEARMONTH and b.USAGECD=x.USAGECD and b.ACQUISITIONCD=x.ACQUISITIONCD and b. VIN =x. VIN and b.OWNERSHIPCD=x.OWNERSHIPCD
and b.COUNTRYCD=x.COUNTRYCD and b.DISCOUNTRYCD and b.ENGINE=x.ENGINE and b.FUELCD=x.FUELCD and b.DISPLACEMENT=x.DISPLACEMENT and b.TRANSMISSIONCD=x.TRANSMISSIONCD and b.GEARCD=x.GEARCD and b. PRICE=x.PRICE
and b.OWNERYEAR=x.OWNERYEAR
select VEHICLECD,MODELCD,BRANDCD,DISBRANDCD,.....,AMOUNT =sum(AMOUNT) from temp_import group by VEHICLECD,MODELCD,BRANDCD,DISBRANDCD,.....
看看速度是否是足够的慢
wangdehao(找找找)
的方法了.
--但是道理是相通得。
--如上面得语句形式,意思就是将temp_import表中得数据先汇总,然后结合MAIN_PAS来进行更新。
--不知道楼主明白不?