case a.ass_term_unit when '01' then (case a.ass_profit_mode when '0' then round(sum(a.ass_amount*a....

转载于 2016-09-29 16:32:00 发布 · 183 阅读

CC 4.0 BY-SA版权

原文链接：http://www.cnblogs.com/huangzhuzhe/p/5920389.html

本文介绍了一个复杂的SQL查询案例，用于计算不同期限单位和利润模式下的总利润，涉及日期筛选、条件分支和数值运算。该查询使用了CASE语句来根据不同的期限单位和利润模式调整计算公式，并对结果进行了四舍五入。

--01 年 02 月 03 日
select a.ass_due_date,
case a.ass_term_unit
when '01' then (case a.ass_profit_mode
when '0' then round(sum(a.ass_amount*a.ass_annual_rate/365*365*a.ass_term/100) ,2)
when '1' then round(sum(a.ass_amount*a.ass_annual_rate/360*365*a.ass_term/100) ,2)
end)
when '02' then (case a.ass_profit_mode
when '0' then round(sum(a.ass_amount*a.ass_annual_rate/365*30*a.ass_term/100) ,2)
when '1' then round(sum(a.ass_amount*a.ass_annual_rate/360*30*a.ass_term/100) ,2)
end)
when '03' then (case a.ass_profit_mode
when '0' then round(sum(a.ass_amount*a.ass_annual_rate/365*a.ass_term/100) ,2)
when '1' then round(sum(a.ass_amount*a.ass_annual_rate/360*a.ass_term/100) ,2)
end)

end
from eam.t_eam_ass a
where a.ass_due_date<'2019-12-24'
GROUP BY a.ass_due_date,a.ass_profit_mode ,a.ass_term_unit