你的位置:首页 > 数据库

[数据库]利用case when 减少表扫描次数


数据库环境:SQL SERVER 2008R2

有网友希望有人帮他优化一下他的SQL,SQL语句如下:

WITH T AS (SELECT B.O_Money MON,B.O_States STATES FROM M_Basket A 
JOIN M_OrderInfo B ON A.OrderID=B.ID WHERE A.GoodID=@GOODSID),B AS (SELECT (SELECT SUM(MON) FROM T)SumMoney,(SELECT SUM(MON) FROM T WHERE STATES IN (2,3,4))ComfirmMoney,(SELECT COUNT(*) FROM T WHERE STATES=2)AleadyDrive,(SELECT COUNT(*) FROM T WHERE STATES=3)AleadyPay,(SELECT COUNT(*) FROM T WHERE STATES=4)AleadyComfirm)INSERT @BIAOSELECT * FROM B

看了一下语句,有可能出问题的地方,是with B 里面的内容,T表被访问了4次,

稍微转换一下思路,用case when来改写这段,就可以让T表只访问1次。

改写的SQL如下:

WITH  T AS ( SELECT  B.O_Money MON ,            B.O_States STATES        FROM   M_Basket A            JOIN M_OrderInfo B ON A.OrderID = B.ID        WHERE  A.GoodID = @GOODSID       ),    B AS ( SELECT  SUM(MON) AS SumMoney ,            SUM(CASE WHEN STATES IN ( 2, 3, 4 ) THEN MON              END) AS ComfirmMoney ,            COUNT(CASE WHEN STATES = 2 THEN 1               END) AS AleadyDrive ,            COUNT(CASE WHEN STATES = 3 THEN 1               END) AS AleadyPay ,            COUNT(CASE WHEN STATES = 4 THEN 1               END) AS AleadyComfirm        FROM   T       )  INSERT @BIAO      SELECT *      FROM  B

(本文完)