薪水表pay
员工号码,国籍,薪水个人所得税表 IncomeTaxRates
ID FROM TO 百分比 速扣数
1 .00 500.00 5.00 .00
2 500.01 2000.99 10.00 25.00
3 2001.00 5000.99 15.00 125.00
4 5001.00 20000.99 20.00 375.00
5 20001.00 40000.99 25.00 1375.00
6 40001.00 60000.99 30.00 3375.00
7 60001.00 80000.99 35.00 6375.00
8 80001.00 100000.99 40.00 10375.00
9 100001.00 10000000.00 45.00 15375.00如果国籍=CN 应缴税额=(薪水-1600)*imcomeTaxRates.百分比/100 - IncomeTaxRatex.速扣数
如果国籍<>CN 应缴税额=(薪水-4800)*imcomeTaxRates.百分比/100 - IncomeTaxRatex.速扣数
百分比和速扣数根据(薪水-1600)或(薪水-4800) 在IncomeTaxRates里的范围.要求得到
pay.员工号码 ,
pay.国籍,pay.薪水,
IncomeTaxRatex.百分比,
IncomeTaxRates.速扣数,
应缴税额
员工号码,国籍,薪水个人所得税表 IncomeTaxRates
ID FROM TO 百分比 速扣数
1 .00 500.00 5.00 .00
2 500.01 2000.99 10.00 25.00
3 2001.00 5000.99 15.00 125.00
4 5001.00 20000.99 20.00 375.00
5 20001.00 40000.99 25.00 1375.00
6 40001.00 60000.99 30.00 3375.00
7 60001.00 80000.99 35.00 6375.00
8 80001.00 100000.99 40.00 10375.00
9 100001.00 10000000.00 45.00 15375.00如果国籍=CN 应缴税额=(薪水-1600)*imcomeTaxRates.百分比/100 - IncomeTaxRatex.速扣数
如果国籍<>CN 应缴税额=(薪水-4800)*imcomeTaxRates.百分比/100 - IncomeTaxRatex.速扣数
百分比和速扣数根据(薪水-1600)或(薪水-4800) 在IncomeTaxRates里的范围.要求得到
pay.员工号码 ,
pay.国籍,pay.薪水,
IncomeTaxRatex.百分比,
IncomeTaxRates.速扣数,
应缴税额
from (select a.*, b.百分比, b.速扣数
from (select 员工号码, 国籍, 薪水, (case 国籍 when 'CN' then 薪水-1600 else 薪水-4800 end) as 薪水2 from pay) a
left join IncomeTaxRates b on a.薪水2 between b.[From] and b.[To]) aa
from (select a.*, b.百分比, b.速扣数
from (select 员工号码, 国籍, 薪水, (case 国籍 when 'CN' then 薪水-1600 else 薪水-4800 end) as 薪水2 from pay) a
left join IncomeTaxRates b on a.薪水2 between b.[From] and b.[To]) aa分三层来实现
1。得到(薪水-1600)或(薪水-4800)
2。得到百分比和速扣数
3。得到应缴税额
a.员工号码 ,
a.国籍,
a.薪水,
b.百分比,
b.速扣数,
应缴税额=(a.薪水-(case a.国籍 when 'CN' then 1600 else 4800 end))*百分比/100-b.速扣数
from
pay a,IncomeTaxRates b
where
(a.薪水-(case a.国籍 when 'CN' then 1600 else 4800 end)) between b.FROM and b.TO
select a.*, b.百分比, b.速扣数, ((case a.国籍 when 'CN' then a.薪水-1600 else a.薪水-4800 end) * b.百分比 /100 - b.速扣数) as 应缴税额
from pay a left join IncomeTaxRates b
on (case a.国籍 when 'CN' then a.薪水-1600 else a.薪水-4800 end) between b.[From] and b.[To]