SQL statement exercise example-find the last two promotions and salaries

Copy codeThe Code is as follows:
-- Programmers are writing an employee report. They need to get information about the current and historical salary status of each employee,
-- To generate reports. The report needs to display the promotion date and salary of each person.
-- If you place each salary information in a row of the result set and have the Host Program format it.
-- Application programmers are a group of lazy people who need to get the current
-- And historical salary information. In this way, you can write a very simple loop statement.
--- Example:
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 data
Insert into salaries
Select 'Tom ', '2017-1-20', 2010
Union
Select 'Tom ', '2017-6-20', 2010
Union
Select 'Tom ', '2017-12-20', 2010
Union
Select 'Tom ', '2017-6-20', 2011
Union
Select 'dick', '2017-6-20 ', 2011
Union
Select 'Harry ', '2017-6-20', 2010
Union
Select 'Harry ', '2017-6-20', 2011

Go

---- Method 1: Use left join for queries (SQL 2000 or later)
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
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 2: This method numbers the rows in each employee, and then extracts the dates closest to the two employment dates,

--- (SQL 2005 or later)
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 3: This method can be used in Versions later than SQL server 2005 by using 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

---- Method 4: Use the view to divide the problem into two situations

--- 1. Only employees with 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
, 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