利用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

（本文完）