年底了,职员都有长薪金的机会,人事部需要做一份报表,就是显示薪金前后值的一份报表。为了实现这样的功能,使用SQL的OUTPUT功能,即可实现原薪金与新薪金比较的报表。
SELECT
[
MemberId
]
,
[
Name
]
,
[
Salary
]
FROM
[dbo].[Member]
结果
MemberId Name Salary
1 Mr. John 2200.00
2 Mr. Joe 2500.00
使用OUTPUT功能实现报表:
代码
UPDATE
[dbo].[Member]
-- 每个职员长5%薪金
SET [ Salary ] = [ Salary ] * ( 1 + 0.05 )
OUTPUT
deleted. [ MemberId ] AS [ MemberId ] ,
deleted. [ Name ] AS [ Name ] ,
deleted. [ Salary ] AS [ OldSalery ] ,
inserted. [ Salary ] AS [ newSalery ]
-- 每个职员长5%薪金
SET [ Salary ] = [ Salary ] * ( 1 + 0.05 )
OUTPUT
deleted. [ MemberId ] AS [ MemberId ] ,
deleted. [ Name ] AS [ Name ] ,
deleted. [ Salary ] AS [ OldSalery ] ,
inserted. [ Salary ] AS [ newSalery ]
结果:
MemberId Name OldSalery newSalery
1 Mr. John 2200.00 2310.000000
2 Mr. Joe 2500.00 2625.000000