利用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 @BIAO
SELECT * 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

（本文完）

转载于:https://www.cnblogs.com/boss-he/p/4647956.html