很久以前准备写的系列文章，后来因为懒一直耽搁着，今天突然决定继续下去，于是有了这篇文章，很基础，但很常用。题目描述依然拷贝。简单来说就是找出个个部门薪水排名第二的人，排名相同的要一起列出来。
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
01.
EmployeeID EmployeeName Department Salary
02.
----------- --------------- --------------- ---------
03.
1 T Cook Finance 40000.00
04.
2 D Michael Finance 25000.00
05.
3 A Smith Finance 25000.00
06.
4 D Adams Finance 15000.00
07.
5 M Williams IT 80000.00
08.
6 D Jones IT 40000.00
09.
7 J Miller IT 50000.00
10.
8 L Lewis IT 50000.00
11.
9 A Anderson Back-Office 25000.00
12.
10 S Martin Back-Office 15000.00
13.
11 J Garcia Back-Office 15000.00
14.
12 T Clerk Back-Office 10000.00
Expected Results
1.
EmployeeID EmployeeName Department Salary
2.
----------- --------------- --------------- ---------
3.
10 S Martin Back-Office 15000.00
4.
11 J Garcia Back-Office 15000.00
5.
2 D Michael Finance 25000.00
6.
3 A Smith Finance 25000.00
7.
7 J Miller IT 50000.00
8.
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 )