2 x781711884 x781711884 于 2016.04.26 15:22 提问

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的语句

1个回答

CSDNXIAOS
CSDNXIAOS   2016.04.26 17:26

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语句
----------------------Hi,地球人,我是问答机器人小S,上面的内容就是我狂拽酷炫叼炸天的答案,除了赞同,你还有别的选择吗?

x781711884
x781711884 看不懂!
一年多之前 回复
Csdn user default icon
上传中...
上传图片
插入图片
准确详细的回答,更有利于被提问者采纳,从而获得C币。复制、灌水、广告等回答会被删除,是时候展现真正的技术了!