數據庫環境：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

（本文完）