TSQL Beginners Challenge 1 - Find the second highest salary for each department

浏览数：25 / 时间：2015年06月12日

很久以前准备写的系列文章，后来因为懒一直耽搁着，今天突然决定继续下去，于是有了这篇文章，很基础，但很常用。题目描述依然拷贝。简单来说就是找出个个部门薪水排名第二的人，排名相同的要一起列出来。

Introduction

The challenge is to find the employees with the second highest salary in each department. However, it is a little more complicated because if two employees have the same salary, you need to list both of them.

Sample Data

EmployeeID  EmployeeName    Department      Salary   
----------- --------------- --------------- ---------
1           T Cook          Finance         40000.00
2           D Michael       Finance         25000.00
3           A Smith         Finance         25000.00
4           D Adams         Finance         15000.00
5           M Williams      IT              80000.00
6           D Jones         IT              40000.00
7           J Miller        IT              50000.00
8           L Lewis         IT              50000.00
9           A Anderson      Back-Office     25000.00
10          S Martin        Back-Office     15000.00
11          J Garcia        Back-Office     15000.00
12          T Clerk         Back-Office     10000.00

Expected Results

EmployeeID  EmployeeName    Department      Salary   
----------- --------------- --------------- ---------
10          S Martin        Back-Office     15000.00
11          J Garcia        Back-Office     15000.00
2           D Michael       Finance         25000.00
3           A Smith         Finance         25000.00
7           J Miller        IT              50000.00
8           L Lewis         IT              50000.00 

Rules

The solution should work on SQL Server 2005 and above.
The output should be ordered by Salary.

The Answe:

if OBJECT_ID(‘Employees‘) is not null 
drop table Employees;
create table Employees  (
    EmployeeID INT IDENTITY,
    EmployeeName VARCHAR(15),
    Department VARCHAR(15),
    Salary NUMERIC(16,2)
)

INSERT INTO Employees(EmployeeName, Department, Salary)
VALUES(‘T Cook‘,‘Finance‘, 40000)
INSERT INTO Employees(EmployeeName, Department, Salary)
VALUES(‘D Michael‘,‘Finance‘, 25000)
INSERT INTO Employees(EmployeeName, Department, Salary)
VALUES(‘A Smith‘,‘Finance‘, 25000)
INSERT INTO Employees(EmployeeName, Department, Salary)
VALUES(‘D Adams‘,‘Finance‘, 15000)

INSERT INTO Employees(EmployeeName, Department, Salary)
VALUES(‘M Williams‘,‘IT‘, 80000)
INSERT INTO Employees(EmployeeName, Department, Salary)
VALUES(‘D Jones‘,‘IT‘, 40000)
INSERT INTO Employees(EmployeeName, Department, Salary)
VALUES(‘J Miller‘,‘IT‘, 50000)
INSERT INTO Employees(EmployeeName, Department, Salary)
VALUES(‘L Lewis‘,‘IT‘, 50000)

INSERT INTO Employees(EmployeeName, Department, Salary)
VALUES(‘A Anderson‘,‘Back-Office‘, 25000)
INSERT INTO Employees(EmployeeName, Department, Salary)
VALUES(‘S Martin‘,‘Back-Office‘, 15000)
INSERT INTO Employees(EmployeeName, Department, Salary)
VALUES(‘J Garcia‘,‘Back-Office‘, 15000)
INSERT INTO Employees(EmployeeName, Department, Salary)
VALUES(‘T Clerk‘,‘Back-Office‘, 10000)

;with cte as 
(
select num= RANK() over(partition by department order by salary desc), * from Employees
)
select EmployeeID,EmployeeName,Department,salary   from cte where num=2

用Rank()函数很轻松就可以达到要求，后来看了下别的方式，觉得用Row_number()也可以，写的代码虽然比较长，但是在思路上却是极好的：

;with cte as 
(
select num= ROW_NUMBER()over(partition by Department order by salary desc ) ,* from Employees
),
cte2 as 
(
select * from cte where num=2
)
select a.EmployeeID,a.EmployeeName,a.Department,a.salary from cte a,cte2 b 
where a.Salary=b.Salary and a.Department=b.Department 
order by Salary

还看到一种方式，说实话没有看懂是怎么个回事，麻烦看到的童鞋给解释下：

  SELECT *
 FROM   Employees
 WHERE  CAST(salary AS VARCHAR(10)) + department IN (
        SELECT  CAST(MAX(salary) AS VARCHAR(10)) + department
        FROM    Employees o
        WHERE   salary < ( SELECT   MAX(salary)
                           FROM     Employees a
                           WHERE    a.department = o.department
                         )
        GROUP BY department )