个税的Oracle一句SQL算法

没啥技术含量，其实就是一个对 model 用法的练习……

WITH AAA AS (
  select 0 AS start_value, 3500 AS end_value, 0.0 ratio from dual
  union select   3500 , 5000, 0.03 from dual
  union select   5000 , 8000, 0.1 from dual
  union select   8000 , 12500, 0.2 from dual
  union select   12500 , 38500, 0.25 from dual
  union select   38500 , 58500, 0.3 from dual
  union select 58500 , 83500, 0.35 from dual
  union select 83500 , NULL, 0.45 from dual
)
SELECT (XX -start_value)*ratio + quick_num FROM (
   SELECT start_value,decode(end_value, 0 , NULL ,end_value) AS end_value,ratio, quick_num FROM (
      select row_number()over(order by start_value) rn,
              nvl(lag(end_value-start_value)over( order by start_value),0)*nvl(lag(ratio)over(order by start_value),0) quick_num,
              start_value, end_value,ratio
        from AAA
) a
  model
   dimension by (rn)
    measures (quick_num,start_value,end_value,ratio) IGNORE NAV
   RULES
     (quick_num[ ANY ] ORDER BY rn = quick_num[cv(rn)- 1] + quick_num[cv(rn)],
      start_value[ ANY ] = start_value[cv(rn)],
      end_value[ ANY ] = end_value[cv(rn)],
      ratio[ ANY ] = ratio[cv(rn)])
)
WHERE XX >= start_value AND ( XX < end_value OR end_value IS NULL )

XX为工资收入扣除各种保险和住房公积金后的数目。总共出现了三个XX，都需要替换。其实做个函数比较好，不过那就不是一句了。