例.列出每个教工税后工资(工资超过2000的缴纳10%所得税,其余的缴纳5%所得税).
解:用两个更新语句如下:
第一:
update PROF
set SAL = SAL * 0.95
where SAL <= 2000
第二:
update PROF
set SAL = SAL * 0.9
where SAL > 2000
我的问题是:怎样用一个更新语句实现上述两个语句的功能。当然,不能用条件表达式CASE WHEN
谢谢!!!!!
解:用两个更新语句如下:
第一:
update PROF
set SAL = SAL * 0.95
where SAL <= 2000
第二:
update PROF
set SAL = SAL * 0.9
where SAL > 2000
我的问题是:怎样用一个更新语句实现上述两个语句的功能。当然,不能用条件表达式CASE WHEN
谢谢!!!!!
set SAL = SAL * a.per
from
(
select per = 0.95, startsal = 0, endsal = 2000
union all
select per = 0.90, startsal = 2000, endsal = 9999999
) a
where PROF.SAL > a.startsal and PROF.SAL <= a.endsal
update prof
set sal=
(case when (SAL <= 2000) then SAL*0.95 else SAL*0.9 end) from tb
insert into prof select 1,2001 union all select 2,1999
go
update prof set sal=sal*(0.95-isnull((select 0.05 from prof where sal>2000 and sal=a.sal),0)) from prof a
go
select * from prof
go
drop table prof
/*
id sal
----------- -----------
1 1800.90
2 1899.05(2 行受影响)
*/
update prof
set sal=
(case when (SAL <= 2000) then SAL*0.95 else SAL*0.9 end) from prof
update prof
set sal=b.sal
from
(select id,sal*0.95 as sal from prof a where sal<=2000
union all
select id,sal*0.9 as sal from prof a where sal>2000)b,prof
where b.id=prof.id1 1800.90
2 1899.05
那这样样子应该可以了