sql语句对比
对比一:
UPDATE A
SET A.GAS_TOTAL_SALE = B.GAS_TOTAL_SALE
FROM #SST_TEMP_GAS_PACK_RPT A, (SELECT GAS_ID, SUM(DAY_SALE_QTY) AS GAS_TOTAL_SALE
FROM SST_DAY_TOTAL
WHERE GAS_ID IN (SELECT GAS_ID FROM #GASTEMPTABLE)
AND RPT_DATE BETWEEN CONVERT(VARCHAR(10),@DATE,120) AND CONVERT(VARCHAR(10),@EDATE,120)
GROUP BY GAS_ID
) B
WHERE A.GAS_ID = B.GAS_ID;
和如下语句对比:
SELECT GAS_ID, SUM(DAY_SALE_QTY) AS GAS_TOTAL_SALE INTO #TEMP1
FROM SST_DAY_TOTAL
WHERE GAS_ID IN (SELECT GAS_ID FROM #GASTEMPTABLE)
AND RPT_DATE BETWEEN CONVERT(VARCHAR(10),@DATE,120) AND CONVERT(VARCHAR(10),@EDATE,120)
GROUP BY GAS_ID;
UPDATE A
SET A.GAS_TOTAL_SALE = B.GAS_TOTAL_SALE
FROM #SST_TEMP_GAS_PACK_RPT A, #TEMP1 B
WHERE A.GAS_ID = B.GAS_ID
DROP TABLE #TEMP1;
可以看出: 第二个语句只是用到了临时表,由于sst_day_total 表的数据量非常大,所以对两中写法进行了测试对比
经过测试得出:
查询一年的数据:
第一个语句执行的时间为:0:03:31
第二个语句执行的时间为:0:03:00