问题描述

sql 有关case when 嵌套case when 语句优化

///////////////////////////////////////////////////////////////////修改前——没添加超过300的语句/////////////////////////////////////////////////////////////////////////////////
select dbo.Base_Integity.ID ,Driver_Name, Mileage,Ultra_oil ,

 case when (ABS (Ultra_oil) > 100 ) and  (Ultra_oil > 0 ) then  80+(Ultra_oil-100)*1  when  ( ABS (Ultra_oil) > 100 ) and  (Ultra_oil < 0 ) then -(80+(ABS(Ultra_oil)-100)*1) else (Ultra_oil*0.8) end as Integrity_Bonus,

dbo.Base_Integity.Remark,dbo.Base_Integity.Month,dbo.Base_Organization.Organization_Name
from Base_Integity
left join Base_Organization on Base_Integity.Organization_ID=Base_Organization.Organization_ID
left join dbo.Base_Driver on Base_Integity.Driver_ID =Base_Driver.Driver_ID

/////////////////////////////////////////////////////////////修改后的添加了的300的语句//////////////////////////////////////////////////////////////////////////////

 select dbo.Base_Integity.ID ,Driver_Name, Mileage,Ultra_oil , 

case when
((case when (ABS (Ultra_oil) > 100 ) and  (Ultra_oil > 0 ) then 80+(Ultra_oil-100)*1 when  ( ABS (Ultra_oil) > 100 ) and  (Ultra_oil < 0 ) then -(80+(ABS(Ultra_oil)-100)*1) else (Ultra_oil*0.8)  end) > 300 ) 

            then  300 

 when  

((case when (ABS (Ultra_oil) > 100 )  and  (Ultra_oil > 0 ) then 80+(Ultra_oil-100)*1 when  ( ABS (Ultra_oil) > 100 ) and  (Ultra_oil < 0 ) then -(80+(ABS(Ultra_oil)-100)*1)  else (Ultra_oil*0.8)  end )<300)

    then  

((case when (ABS (Ultra_oil) > 100 )  and  (Ultra_oil > 0 ) then 80+(Ultra_oil-100)*1 when  ( ABS (Ultra_oil) > 100 ) and  (Ultra_oil < 0 ) then -(80+(ABS(Ultra_oil)-100)*1)  else (Ultra_oil*0.8)  end)) 

else (Ultra_oil*0.8) end  

  as Integrity_Bonus , 

     --超过300语句
 case when
((case when (ABS (Ultra_oil) > 100 )  and  (Ultra_oil > 0 ) then 80+(Ultra_oil-100)*1 when  ( ABS (Ultra_oil) > 100 ) and  (Ultra_oil < 0 ) then -(80+(ABS(Ultra_oil)-100)*1)  else (Ultra_oil*0.8)  end )>300) 

          then  

((case when (ABS (Ultra_oil) > 100 )  and  (Ultra_oil > 0 ) then 80+(Ultra_oil-100)*1 when  ( ABS (Ultra_oil) > 100 ) and  (Ultra_oil < 0 ) then -(80+(ABS(Ultra_oil)-100)*1)  else (Ultra_oil*0.8)  end )-300) 

            when  

((case when (ABS (Ultra_oil) > 100 )  and  (Ultra_oil > 0 ) then 80+(Ultra_oil-100)*1 when  ( ABS (Ultra_oil) > 100 ) and  (Ultra_oil < 0 ) then -(80+(ABS(Ultra_oil)-100)*1)  else (Ultra_oil*0.8)  end )<300) 

    then  0 else null end as Remark,

dbo.Base_Integity.Month,dbo.Base_Organization.Organization_Name
            from Base_Integity
            left join Base_Organization on Base_Integity.Organization_ID=Base_Organization.Organization_ID
            left join dbo.Base_Driver on Base_Integity.Driver_ID =Base_Driver.Driver_ID 

///////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////
 即：小于等于100升的，奖金(Integrity_Bonus)=节超油( Ultra_oil )*0.8
      大于100升的,奖金=80+（节超油-100）*1
     节超奖金大于300元的按300元算，超出部分记录备注(Remark)
        优化一下添加了超过300的语句

解决方案

select
(
case
when o.class_id='10' then (select m.busi_id from pd_busi_instance_inter m where m.oss_busi_id=#ossBusiId#)......
答案就在这里：SQL的case when语句