case when

 Select ordernumber,
    Earning = case when OrderNumber like '%PP%' then convert(int,Earning+100000)
            else Earning end 
from ExeclPayResult 
where inputTime between'2014-01-05' and '2014-01-05 23:59:59'

 

 

 

select  Id,OrderNumber,startTime, Earning ,inputTime ,(Earning-resultEarning) as Pricediff  from( select  Id,OrderNumber,startTime, resultEarning ,inputTime ,Earning as qwe, Earning= case when OrderNumber like '%PP%' then convert(money,Earning+resultEarning*0.003)  else Earning end  from(select L2.Id,L2.OrderNumber,L2.startTime,L2.Earning,isnull(L1.Earning,0) as resultEarning ,L2.inputTimefrom(select * from NewPayResult where EndTime between '" + Convert.ToDateTime(startTime).AddDays(-1) + "' and '" + Convert.ToDateTime(endTime).AddDays(1) + "') as L1 right join (select * from ExeclPayResult where inputTime between'" + startTime + "' and '" + endTime + "') as L2 on ltrim(rtrim(L1.OrderNumber))=ltrim(rtrim(L2.OrderNumber)) where L1.OrderNumber is null or (L2.Earning-isnull(L1.Earning,0))!=0 ) as reslut )as OutMore  where  Earning-resultEarning>0.01 or  Earning-resultEarning<-0.01

转载于:https://www.cnblogs.com/noert/p/3524887.html