sqlserver 经典sql语句case

旧城等待， 2022-06-01 01:54 411阅读 0赞

select  (alipay\_order\_num + succ\_refund\_order\_num)  ,case  (alipay\_order\_num+succ\_refund\_order\_num) when 0 then 1 else  (alipay\_order\_num+succ\_refund\_order\_num) end   from ysf\_ShopTrade nolock  
  
  
select   
case succ\_refund\_order\_num when 0 then 1 else succ\_refund\_order\_num end as num ,  
case  (alipay\_order\_num+succ\_refund\_order\_num) when 0 then 1 else  (alipay\_order\_num+succ\_refund\_order\_num)  end as two   ,  
case succ\_refund\_order\_num when 0 then 1 else succ\_refund\_order\_num end /case  (alipay\_order\_num+succ\_refund\_order\_num) when 0 then 1 else  (alipay\_order\_num+succ\_refund\_order\_num)   end from  ysf\_ShopTrade  
  
  
select  case when (alipay\_order\_num+succ\_refund\_order\_num)=0 then 0 else (alipay\_order\_num+succ\_refund\_order\_num)   end  from ysf\_ShopTrade  
                      
  
  
               SELECT  A.seller\_id ,  
                    A.thedate ,  
                    succ\_trade\_amt AS '已收货金额' ,  
                    succ\_trade\_num AS '交易成功笔数 ' ,  
                    succ\_auction\_num AS '已收货商品数' ,  
                    succ\_refund\_trade\_amt AS '退款成功金额' ,  
                    case when (alipay\_order\_num+succ\_refund\_order\_num)=0 then 0  
                    else CONVERT(DECIMAL(18, 4), CONVERT(DECIMAL(18, 4), succ\_refund\_order\_num/ CONVERT(DECIMAL(18, 4), (alipay\_order\_num+succ\_refund\_order\_num)))) end  AS '七天完结率'  
            FROM    \[dbo\].\[ysf\_ShopTrade\] A  
  
  
select   
seller\_id,  
thedate,  
convert(decimal(18,4), succ\_trade\_amt)  AS '已收货金额' ,  
succ\_trade\_num AS '交易成功笔数 ' ,  
succ\_auction\_num AS '已收货商品数' ,  
succ\_refund\_trade\_amt AS '退款成功金额' ,  
1-(case when (alipay\_order\_num+succ\_refund\_order\_num)=0 then 0 else convert(decimal(18,4),convert(decimal(18,4), succ\_refund\_order\_num/convert(decimal(18,4),(alipay\_order\_num+succ\_refund\_order\_num))))    end) '七天完结率' from ysf\_ShopTrade  where seller\_id='601879343' and thedate between 20151222  and 20151227