一道求職試題.關於數據庫的

[tingquan]

一道求職試題.關於數據庫的
題如下:
个人所得税税率表一
级 数 全 月 应 纳 税 所 得 额 税率（%）
1 不超过500元的 5
2 超过500元至2000元的部分 10
3 超过2000元至5000元的部分 15
4 超过5000元至20000元的部分 20
5 超过20000元至40000元的部分 25
6 超过40000元至60000元的部分 30
7 超过60000元至80000元的部分 35
8 超过80000元至100000元的部分 40
9 超过100000元的部分 45
若有工资表为:
部门 姓名 应发工资
制造一课 张三 1750
制造二课 李四 2130




请用SQL语句获得如下查询结果，不可以用存储过程！
生成结果有五个字段，部门，姓名，应发工资，个人所得税，实发工资

[tingquan]

大家快點啊.答案正確就給分.

[OpenVMS]

declare @taxrate real
select 部门,姓名,应发工资,应发工资*@taxrate=
case
when 应发工资<=500 then 10/100
when 应发工资>500 and 应发工资 <=2000 then 10/100
when 应发工资>2000 and 应发工资 <=5000 then 15/100
when 应发工资>5000 and 应发工资 <=20000 then 20/100
when 应发工资>20000 and 应发工资 <=40000 then 25/100
when 应发工资>40000 and 应发工资 <=60000 then 30/100
when 应发工资>60000 and 应发工资 <=80000 then 35/100
when 应发工资>80000 and 应发工资 <=10000 then 40/100
when 应发工资>100000 then 45/100
end AS 个人所得税,
应发工资*(1-@taxrate ) AS 实发工资
from 工资表

---如果个人所得税税率表是个动态数据库表,则要建一个临时表,从字符串中分离----出上下限数字,然后查询两张表.

[KingSunSha]

在oracle下完成结果如下：
SQL> select * from tax_rates;

TAX_LEVEL SALARY_FROM SALARY_TO TAX_RATE
---------- ----------- ---------- ----------
1 500 2000 .1
2 2000 5000 .15
3 5000 20000 .2
4 20000 40000 .25
5 40000 60000 .3
6 60000 80000 .35
7 80000 100000 .4
8 100000 9999999999 .45
0 0 500 .05

9 rows selected.

SQL> select * from salary;

DEPT_NAME EMP_NAME SALARY
---------- -------------------- ----------
DEPT A EMP 1 1750
DEPT B EMP 2 2130
DEPT C EMP 3 4000

SQL> select dept_name, emp_name, salary,
2 sum(decode(sign(salary - salary_from), -1 ,0,
3 decode(sign(salary_to - salary), -1, (salary_to - salary,
4 (salary - salary_from) * tax_rate))) tax
5 from salary x, tax_rates y
6 group by dept_name, emp_name, salary;

DEPT_NAME EMP_NAME SALARY TAX
---------- -------------------- ---------- ----------
DEPT A EMP 1 1750 150
DEPT B EMP 2 2130 194.5
DEPT C EMP 3 4000 475

[KingSunSha]

在oracle下完成结果如下：
SQL> select * from tax_rates;

TAX_LEVEL SALARY_FROM SALARY_TO TAX_RATE
---------- ----------- ---------- ----------
1 500 2000 .1
2 2000 5000 .15
3 5000 20000 .2
4 20000 40000 .25
5 40000 60000 .3
6 60000 80000 .35
7 80000 100000 .4
8 100000 9999999999 .45
0 0 500 .05

9 rows selected.

SQL> select * from salary;

DEPT_NAME EMP_NAME SALARY
---------- -------------------- ----------
DEPT A EMP 1 1750
DEPT B EMP 2 2130
DEPT C EMP 3 4000

SQL> select dept_name, emp_name, salary,
2 sum(decode(sign(salary - salary_from), -1 ,0,
3 decode(sign(salary_to - salary), -1, (salary_to - salary,
4 (salary - salary_from) * tax_rate))) tax
5 from salary x, tax_rates y
6 group by dept_name, emp_name, salary;

DEPT_NAME EMP_NAME SALARY TAX
---------- -------------------- ---------- ----------
DEPT A EMP 1 1750 150
DEPT B EMP 2 2130 194.5
DEPT C EMP 3 4000 475

[Yang_]

select 部门,姓名,应发工资,
case
when 应发工资<=500 then 应发工资*5/100
when 应发工资>500 and 应发工资 <=2000 then 500*0.05+(应发工资-500)*0.1
when 应发工资>2000 and 应发工资 <=5000 then 500*0.05+1500*0.1+(应发工资-2000)*0.15
when 应发工资>5000 and 应发工资 <=20000 then 500*0.05+1500*0.1+3000*0.15+(应发工资-5000)*0.20
when 应发工资>20000 and 应发工资 <=40000 then 500*0.05+1500*0.1+3000*0.15+15000*0.20+(应发工资-20000)*0.25
when 应发工资>40000 and 应发工资 <=60000 then 500*0.05+1500*0.1+3000*0.15+15000*0.20+20000*0.25+(应发工资-40000)*0.30
when 应发工资>60000 and 应发工资 <=80000 then 500*0.05+1500*0.1+3000*0.15+15000*0.20+20000*0.25+20000*0.30+(应发工资-60000)*0.35
when 应发工资>80000 and 应发工资 <=10000 then 500*0.05+1500*0.1+3000*0.15+15000*0.20+20000*0.25+20000*0.30+20000*0.35+(应发工资-80000)*0.40
when 应发工资>100000 then 500*0.05+1500*0.1+3000*0.15+15000*0.20+20000*0.25+20000*0.30+20000*0.35+20000*0.40+(应发工资-100000)*0.45
end AS 个人所得税,应发工资-(
case
when 应发工资<=500 then 应发工资*5/100
when 应发工资>500 and 应发工资 <=2000 then 500*0.05+(应发工资-500)*0.1
when 应发工资>2000 and 应发工资 <=5000 then 500*0.05+1500*0.1+(应发工资-2000)*0.15
when 应发工资>5000 and 应发工资 <=20000 then 500*0.05+1500*0.1+3000*0.15+(应发工资-5000)*0.20
when 应发工资>20000 and 应发工资 <=40000 then 500*0.05+1500*0.1+3000*0.15+15000*0.20+(应发工资-20000)*0.25
when 应发工资>40000 and 应发工资 <=60000 then 500*0.05+1500*0.1+3000*0.15+15000*0.20+20000*0.25+(应发工资-40000)*0.30
when 应发工资>60000 and 应发工资 <=80000 then 500*0.05+1500*0.1+3000*0.15+15000*0.20+20000*0.25+20000*0.30+(应发工资-60000)*0.35
when 应发工资>80000 and 应发工资 <=10000 then 500*0.05+1500*0.1+3000*0.15+15000*0.20+20000*0.25+20000*0.30+20000*0.35+(应发工资-80000)*0.40
when 应发工资>100000 then 500*0.05+1500*0.1+3000*0.15+15000*0.20+20000*0.25+20000*0.30+20000*0.35+20000*0.40+(应发工资-100000)*0.45
end) AS 实发工资
from 工资表

[KingSunSha]

翻译成sql server的t/sql：
select dept_no, emp_name, salary,
sum(case when salary < salary_from
then 0
else case when salary > salary_to
then (salary_to - salary) * tax_rate
else (salary - salary_from) * tax_rate
end
end)
from salary x, tax_rates y
group by dept_no, emp_name, salary

[Yang_]

借用三千兄的表结构和算法，SQL SERVER的写法：

select dept_name, emp_name, salary,
sum(case when salary > salary_from then
case when salary_to > salary then (salary-salary_from)*tax_rate
else (salary_to-salary_from)*tax_rate
end
else 0
end) as tax
into #Temp
from salary x, tax_rates y
group by dept_name, emp_name, salary

select dept_name, emp_name, salary,tax,salary-tax as Realsalary
from #Temp

[Yang_]

呵呵，写了半天，三千兄早写完了！！
PFPF!!

[tingquan]

但是如果你的工資是4000圓,那你應該交的稅是(500*0.05+(2000-500)*0.10+(4000-2000)*0.15),並不只是簡單的查詢.另外,稅率表是個數據表.幫幫我!

[tingquan]

KingSunSha(弱水三千)兄; ( ) 信
答案小有錯誤(Sql Server),但給我們指明了方向.
Yang_(扬帆破浪) 兄,是第一個實踐者,同樣我也很PFPF!! 能否不用臨時表呢!!
分我一定給,但是如何分配呢,大家給題個意見!

[OpenVMS]

不知道还要分段纳税 :-)

[tingquan]

沒關係了,多謝大家捧場!