Wayne


Calendar
February | ||||||
---|---|---|---|---|---|---|
Sun | Mon | Tue | Wed | Thu | Fri | Sat |
29 | 30 | 31 | 1 | 2 | 3 | 4 |
5 | 6 | 7 | 8 | 9 | 10 | 11 |
12 | 13 | 14 | 15 | 16 | 17 | 18 |
19 | 20 | 21 | 22 | 23 | 24 | 25 |
26 | 27 | 28 | 1 | 2 | 3 | 4 |

Categories

Search

Random Posts

Counter
362405

Hot Posts

New Comments

New Messages

Links

RSS

个税的Oracle一句SQL算法
Wayne
posted @ Tue, 16 Oct 2012 12:19:42 +0000
in Experience
, 2405 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,都需要替换。其实做个函数比较好,不过那就不是一句了。
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.
Fri, 10 Jul 2020 07:20:26 +0000
Very nice & knowledgeable post all features are defined in a good way