Reduce the number of table scans with case

Original: Reduce the number of table scans with case

Database environment: SQL SERVER 2008R2

Some netizens want someone to help him optimize his sql,sql statement as follows:

 withT as (SELECTB.o_money mon,b.o_states states fromM_basket A
JOINM_orderinfo B onA.orderid=b.IDWHEREA.goodid=@GOODSID), B as (SELECT(SELECT SUM(MON) fromT) Summoney, (SELECT SUM(MON) fromTWHEREStatesinch(2,3,4)) Comfirmmoney, (SELECT COUNT(*) fromTWHEREStates=2) aleadydrive, (SELECT COUNT(*) fromTWHEREStates=3) Aleadypay, (SELECT COUNT(*) fromTWHEREStates=4) aleadycomfirm)INSERT @BIAOSELECT *  fromB

Look at the statement, there may be a problem, is the contents of with B, the T table was visited 4 times,

Change your mind a little bit and rewrite the paragraph with case time to make the T table accessible only 1 times.

The rewritten SQL is as follows:

 withT as(SELECTB.o_money MON, b.o_states states fromM_basket AJOINM_orderinfo B onA.orderid=b.idWHEREA.goodid= @GOODSID), B as(SELECT   SUM(MON) asSummoney,SUM( Case  whenStatesinch(2,3,4) ThenMONEND) asComfirmmoney,COUNT( Case  whenStates= 2  Then 1                              END) asaleadydrive,COUNT( Case  whenStates= 3  Then 1                              END) asAleadypay,COUNT( Case  whenStates= 4  Then 1                              END) asaleadycomfirm fromT)INSERT  @BIAO            SELECT  *             fromB

(End of this article)

Reduce the number of table scans with case