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

（本文完）