做题笔记：SQL Sever 方式做牛客SQL的题目--SQL212

----SQL212 获取当前薪水第二多的员工的emp_no以及其对应的薪水salary
查找薪水排名第二多的员工编号emp_no、薪水salary、last_name以及first_name，不能使用order by完成，
以上例子输出为：emp_no salary last_name first_name

表的创建和数据的插入：

drop table if exists  employees; 
drop table if exists  salaries ; 
CREATE TABLE employees (
emp_no int NOT NULL,
birth_date date NOT NULL,
first_name varchar(14) NOT NULL,
last_name varchar(16) NOT NULL,
gender char(1) NOT NULL,
hire_date date NOT NULL,
PRIMARY KEY (emp_no));
CREATE TABLE salaries (
emp_no int NOT NULL,
salary int NOT NULL,
from_date date NOT NULL,
to_date date NOT NULL,
PRIMARY KEY (emp_no,from_date));
INSERT INTO employees VALUES(10011,'1953-09-02','Heorgi','Gacello','M','1986-06-26');
INSERT INTO employees VALUES(10012,'1964-06-02','Cezalel','Dimmel','F','1985-11-21');
INSERT INTO employees VALUES(10013,'1959-12-03','Aarto','Camford','M','1986-08-28');
INSERT INTO employees VALUES(10014,'1954-05-01','Dhirstian','Loblick','M','1986-12-01');
INSERT INTO employees VALUES(10001,'1953-09-02','Georgi','Facello','M','1986-06-26');
INSERT INTO employees VALUES(10002,'1964-06-02','Bezalel','Simmel','F','1985-11-21');
INSERT INTO employees VALUES(10003,'1959-12-03','Parto','Bamford','M','1986-08-28');
INSERT INTO employees VALUES(10004,'1954-05-01','Chirstian','Koblick','M','1986-12-01');
INSERT INTO employees VALUES(10005,'1955-01-21','Kyoichi','Maliniak','M','1989-09-12');
INSERT INTO employees VALUES(10006,'1953-04-20','Anneke','Preusig','F','1989-06-02');
INSERT INTO employees VALUES(10007,'1957-05-23','Tzvetan','Zielinski','F','1989-02-10');
INSERT INTO employees VALUES(10008,'1958-02-19','Saniya','Kalloufi','M','1994-09-15');
INSERT INTO employees VALUES(10009,'1952-04-19','Sumant','Peac','F','1985-02-18');
INSERT INTO employees VALUES(10010,'1963-06-01','Duangkaew','Piveteau','F','1989-08-24');
INSERT INTO employees VALUES(10011,'1953-11-07','Mary','Sluis','F','1990-01-22');
INSERT INTO salaries VALUES(10001,88958,'2002-06-22','9999-01-01');
INSERT INTO salaries VALUES(10002,72527,'2001-08-02','9999-01-01');
INSERT INTO salaries VALUES(10003,43311,'2001-12-01','9999-01-01');
INSERT INTO salaries VALUES(10004,74057,'2001-11-27','9999-01-01');
INSERT INTO salaries VALUES(10005,94692,'2001-09-09','9999-01-01');
INSERT INTO salaries VALUES(10006,43311,'2001-08-02','9999-01-01');
INSERT INTO salaries VALUES(10007,88070,'2002-02-07','9999-01-01');
INSERT INTO salaries VALUES(10009,95409,'2002-02-14','9999-01-01');
INSERT INTO salaries VALUES(10010,94409,'2001-11-23','9999-01-01');
INSERT INTO salaries VALUES(10008,94692,'2002-06-22','9999-01-01');
INSERT INTO salaries VALUES(10011,72527,'2001-08-02','9999-01-01');
INSERT INTO salaries VALUES(10012,94692,'2001-12-01','9999-01-01');
INSERT INTO salaries VALUES(10013,74057,'2001-11-27','9999-01-01');
INSERT INTO salaries VALUES(10014,94692,'2001-11-27','9999-01-01');

解题思路：找到第二高工资，联接员工信息表和工资表，筛选出第二高工资的员工
1、查询如下：

 select e.emp_no,s.salary,e.last_name,e.first_name
 from employees e
 join salaries s on e.emp_no = s.emp_no
 where s.salary = (
				 select max(salary) as salary		--查询排除掉s1后工资表最高工资，就是第二高了
				 from salaries
				 where salary <> (
								 select max(salary) as salary	--查询工资表最高工资s1
								 from salaries					
									) 
					)

此查询先查询出最高工资，在此查询排除最高工资后的最高工资就是第二高工资了，联接两表查询即可，但是这个查询有弊端就是如果求排名越后的代码越复杂，需要优化查询；
2、可使用自连接查询：

 select e.emp_no,s.salary,e.last_name,e.first_name
 from employees e
 join salaries s on e.emp_no = s.emp_no
 where s.salary = (
					select s1.salary
					from salaries s1 join salaries s2      -- 自连接查询
					on s1.salary <= s2.salary
					group by s1.salary                     -- 当s1<=s2链接并以s1.salary分组时一个s1会对应多个s2
					having count(distinct s2.salary) = 2	---- 去重之后的数量就是对应的名次					
					)

此查询不会受排名的限制，count( )等于几就可求第几名的数据。

如果题目没有制约，可以用order by子句的话会相对简便
3、使用top和order by筛选数据

 select e.emp_no,s.salary,e.last_name,e.first_name
 from employees e
 join salaries s on e.emp_no = s.emp_no
 where s.salary = (
				select top(1) * 
				from (
						select distinct top(2) salary 
						from salaries 
						order by salary desc
						) t
				order by salary)

此查询思路是先查询前两名的工资(降序)后，再(升序)查询前一名的工资，就可以获取第二高工资。

4、使用窗体函数，如果不限制order by子句的话推荐使用，方便简洁：

 select emp_no,salary,first_name,last_name
 from (
		 select e.emp_no,salary,first_name,last_name,
				dense_rank() over(order by salary desc) as rank_num
		 from employees e
		 join salaries a
		 on e.emp_no = a.emp_no		
		) t
 where rank_num = 2

此查询中：窗体函数对联接两表后的数据工资进行降序排序，再嵌套查询排名第二的工资，使用窗体函数容易理解，思路会更清晰。