Heim  >  Artikel  >  Datenbank  >  SQL语句练习实例之一找出最近的两次晋升日期与工资额

SQL语句练习实例之一找出最近的两次晋升日期与工资额

WBOY
WBOYOriginal
2016-06-07 18:02:50810Durchsuche

程序员们在编写一个雇员报表,他们需要得到每个雇员当前及历史工资状态的信息,以便生成报表。报表需要显示每个人的晋升日期和工资数目。

代码如下:
--程序员们在编写一个雇员报表,他们需要得到每个雇员当前及历史工资状态的信息,
--以便生成报表。报表需要显示每个人的晋升日期和工资数目。
--如果将每条工资信息都放在结果集的一行中,并让宿主程序去格式化它。
--应用程序的程序员都是一帮懒人,他们需要在每个雇员的一行上得到当前
--和历史工资信息。这样就可以写一个非常简单的循环语句。
---示例:
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

----方法一、使用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
----方法二、这个方法是对每个雇员中的行进行编号,然后取出两个雇用日期最近的日期,

---(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

go
---方法三、在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

----方法四、使用视图,将问题分为两种情况

---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_datehaving COUNT(*)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
Stellungnahme:
Der Inhalt dieses Artikels wird freiwillig von Internetnutzern beigesteuert und das Urheberrecht liegt beim ursprünglichen Autor. Diese Website übernimmt keine entsprechende rechtliche Verantwortung. Wenn Sie Inhalte finden, bei denen der Verdacht eines Plagiats oder einer Rechtsverletzung besteht, wenden Sie sich bitte an admin@php.cn