mysql的case when then else end语法的例子。

实例sql：

select distinct
   now()                    as `Data_Dt`
  ,count(*)                 as `Orders_close`
  ,sum(a.Order_Amt)         as `Orders_Amt`
  ,count(case when a.Order_PayType=2 then 1 else 0 end)         as `PayCount_Weixin`
  ,count(case when a.Order_PayType=0 then 1 else 0 end)         as `PayCount_Cash`
  ,count(case when a.giftPrice>0  then 1 else 0 end)         as `RedPockets_Used`
  ,sum(case when a.giftPrice>0 then a.giftPrice else 0 end)      as `RedPocketsAmt_Used`
  ,a.market                 as `Market_Id`
from ${MY_SourceDB}.order_info a
where 
  TIMESTAMPDIFF(SECOND,a.CreateTime,now())> 0
  and TIMESTAMPDIFF(SECOND,a.CreateTime,now())<= 3600*24
  and (a.Runman_id not in(1,58,83) or a.Runman_Id is null)
  and a.Order_Status=4000
group by a.market  
;

///

SELECT * FROM test5 ;
    RESOURCES DATETIME CNT ID
1 0 2018-01-22 4 1
2 0 2018-01-24 10 2
3 0 2018-01-25 25 3
4 0 2018-01-26 14 4
5 0 2018-01-29 3 5
6 1 2018-01-29 22 6
7 2 2018-01-29 1 7
8 1 2018-01-30 44 8
9 2 2018-01-30 7 9
10 2 2018-01-31 1 10
11 2 2018-02-01 1 11

SELECT resources,case when resources=0 then sum(cnt) 
when  resources=1 then sum(cnt)  
else 0  end 
 FROM test5
group by resources
;

    RESOURCES CASEWHENRESOURCES=0THENSUM(CNT)WHENRESOURCES=1THENSUM(CNT)ELSE0END
1 1 66
2 0 56
3 2 0