///////////////////////////////////////////////////////////////////修改前——没添加超过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的语句