个税的Oracle一句SQL算法

Wayne posted @ Tue, 16 Oct 2012 12:19:42 +0000 in Experience , 2392 readers

没啥技术含量,其实就是一个对 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,都需要替换。其实做个函数比较好,不过那就不是一句了。

Oracle 1z0-1072 Exam said:
Tue, 30 Jun 2020 11:40:46 +0000

Oracle Oracle Cloud Solutions Infrastructure certification lead you to numerous opportunities in career development and shaping your future. Dumpsleader is your ally in achieving your targeted Oracle Cloud Solutions Infrastructure certification, providing you easy and interactive 1z0-1072 exam dumps.

Scew said:
Fri, 10 Jul 2020 07:20:26 +0000

Very nice & knowledgeable post all features are defined in a good way


Login *


loading captcha image...
(type the code from the image)
or Ctrl+Enter