SQL語句演習實例之一——找出比來的兩次提升日期與工資額。本站提示廣大學習愛好者：（SQL語句演習實例之一——找出比來的兩次提升日期與工資額）文章只能為提供參考，不一定能成為您想要的結果。以下是SQL語句演習實例之一——找出比來的兩次提升日期與工資額正文
--法式員們在編寫一個雇員報表，他們須要獲得每一個雇員以後及汗青工資狀況的信息，
--以便生成報表。報表須要顯示每一個人的提升日期和工資數量。
--假如將每條工資信息都放在成果集的一行中，並讓宿主法式去格局化它。
--運用法式的法式員都是一幫懶人，他們須要在每一個雇員的一行上獲得以後
--和汗青工資信息。如許便可以寫一個異常簡略的輪回語句。
---示例：
create table salaries
( name nvarchar(50) not null,
sal_date date not null,
salary money not null,
)
go
ALTER TABLE [dbo].salaries ADD CONSTRAINT [PK_salaries] PRIMARY KEY CLUSTERED
(
name ,sal_date asc
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF,
SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON,
ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO
----拔出數據
insert into salaries
select 'TOM','2010-1-20',2000
union
select 'TOM','2010-6-20',2300
union
select 'TOM','2010-12-20',3000
union
select 'TOM','2011-6-20',4000
union
select 'Dick','2011-6-20',2000
union
select 'Harry','2010-6-20',2000
union
select 'Harry','2011-6-20',2000
go
----辦法1、應用left join 銜接停止查詢（sql 2000及以上版本）
select b.name,b.maxdate,y.salary,b.maxdate2,z.salary
from(select a.name,a.maxdate,MAX(x.sal_date) as maxdate2
from(select w.name,MAX(w.sal_date) as maxdate
from salaries as w
group by w.name) as a
left outer join salaries as x on a.name=x.name and a.maxdate>x.sal_date
group by a.name,a.maxdate) as b
left outer join salaries as y
on b.name=y.name and b.maxdate=y.sal_date
left outer join salaries as z
on b.name=z.name and b.maxdate2=z.sal_date
go
----辦法2、這個辦法是對每一個雇員中的行停止編號，然後掏出兩個招聘日期比來的日期，
---（sql 2005以上版本）
select s1.name,
MAX(case when rn=1 then sal_date else null end) as curr_date,
MAX(case when rn=1 then salary else null end) as curr_salary,
MAX(case when rn=2 then sal_date else null end) as prev_date,
MAX(case when rn=2 then salary else null end) as curr_salary
from (select name,sal_date,salary, RANK() over(partition by name order by sal_date desc) rn
from salaries
) s1 where rn<3 group by s1.name
go
---辦法3、在sql server 2005以後版本可使用這類辦法 ，應用CTE的方法來完成
with cte(name,sal_date,sal_amt,rn)
as
(
select name,sal_date,salary,ROW_NUMBER() over(PARTITION by name order by sal_date desc) as rn from salaries
)
select o.name,o.sal_date AS curr_date,o.sal_amt as curr_amt,i.sal_date as prev_date ,i.sal_amt as prev_amt from cte as o
left outer join cte as i on o.name=i.name and i.rn=2 where o.rn=1
go
----辦法4、應用視圖，將成績分為兩種情形
---1.只要一次工資更改的雇員
---2.有兩次或屢次工資更改的雇員
create view v_salaries
as
select a.name,a.sal_date,MAX(a.salary) as salary from salaries as a ,salaries as b
where a.sal_date<=b.sal_date and a.name=b.name group by a.name,a.sal_date
having COUNT(*)<=2
go
select a.name,a.sal_date, a.salary,b.sal_date,b.salary from v_salaries a
,v_salaries b
where a.name=b.name and a.sal_date>b.sal_date
union all
select name,max(sal_date),max(salary),cast(null as date),cast(null as decimal(8,2))
from v_salaries
group by name
having count(*)=1
go
drop table salaries
go
drop view v_salaries
