Copy Code code as follows:
-Programmers are writing an employee report that they need to get information about the current and historical wage status of each employee,
--to generate a report. The report needs to show the promotion date and the number of salaries for each person.
-If each payroll information is placed on a row of the result set, and the host program formats it.
Application programmers are a bunch of lazy people who need to get the current
--and historical wage information. This allows you to write a very simple loop statement.
---example:
CREATE TABLE Salaries
(name nvarchar () NOT NULL,
Sal_date date NOT NULL,
Salary money is 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
Inserting data----
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
----method One, query using a LEFT JOIN connection (SQL 2000 and above)
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
----Method Two, this method is to number the rows in each employee, and then remove the last date of the two hire date,
---(SQL 2005 version above)
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
---method three, after SQL Server 2005 version can use this method, using a CTE method to implement
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
----method Four, use the view, divides the question into two kinds of situations
---1. Employees with only one wage change
---2. Employees with two or more wage changes
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
