SQL巧用表的自连接和运算符代替排序的几个例子

MySQL巧用表的自连接和运算符代替排序的几个例子

例1： SQL18

获取当前薪水第二多的员工的emp_no以及其对应的薪水salary
步骤一：自连接并筛选s1.salary <= s2.salary的行

SELECT * FROM salaries AS s1
JOIN salaries AS s2 ON s1.salary <= s2.salary

10004	74057	2001-11-27	9999-01-01	10001	88958	2002-06-22
10003	43311	2001-12-01	9999-01-01	10001	88958	2002-06-22
10002	72527	2001-08-02	9999-01-01	10001	88958	2002-06-22
10001	88958	2002-06-22	9999-01-01	10001	88958	2002-06-22
10003	43311	2001-12-01	9999-01-01	10002	72527	2001-08-02
10002	72527	2001-08-02	9999-01-01	10002	72527	2001-08-02
10003	43311	2001-12-01	9999-01-01	10003	43311	2001-12-01
10004	74057	2001-11-27	9999-01-01	10004	74057	2001-11-27
10003	43311	2001-12-01	9999-01-01	10004	74057	2001-11-27
10002	72527	2001-08-02	9999-01-01	10004	74057	2001-11-27

步骤二：查找第二多的工资是多少

SELECT s1.salary FROM salaries AS s1
JOIN salaries AS s2 ON s1.salary <= s2.salary
GROUP BY s1.salary
HAVING COUNT(s2.salary) = 2

步骤三：完善外层查询后的最终代码

SELECT employees.emp_no, salaries.salary,
    employees.last_name, employees.first_name
    
FROM employees JOIN salaries ON
employees.emp_no = salaries.emp_no

WHERE salaries.salary = (
    SELECT s1.salary FROM salaries AS s1
    JOIN salaries AS s2 ON s1.salary <= s2.salary
    GROUP BY s1.salary
    HAVING COUNT(s2.salary) = 2
    )

例2：SQL23

对所有员工的薪水按照salary降序进行1-N的排名

-- rank排名：查询表中大于自己薪水的员工的数量（考虑并列：去重）
SELECT 
  s1.emp_no,
  s1.salary,
  (SELECT 
    COUNT(DISTINCT s2.salary) 
  FROM
    salaries s2 
  WHERE s2.to_date = '9999-01-01' 
    AND s2.salary >= s1.salary) AS `rank`  -- 去重：计算并列排名
FROM
  salaries s1 
WHERE s1.to_date = '9999-01-01' 
ORDER BY s1.salary DESC,
  s1.emp_no ;

也可以使用窗口函数

select emp_no, salary,
       dense_rank() over (order by salary desc) as rank
from salaries
where to_date='9999-01-01'
order by rank asc,emp_no asc;

例3：SQL87

最差是第几名(一)

步骤一： 连接，做笛卡尔积，筛选T2表中比T1小的

SELECT T1.*, T2.*
FROM class_grade T1
JOIN class_grade T2 ON T2.grade <= T1.grade
ORDER BY T1.grade, T2.grade

结果为：

T1.grade	T1带来的无用列	T2.grade
A	2	A	2
B	2	A	2
B	2	B	2
C	2	A	2
C	2	B	2
C	2	C	2
D	1	A	2
D	1	B	2
D	1	C	2
D	1	D	1

步骤二： 按T2.grade分组再求和

SELECT T1.grade, SUM(T2.number) AS t_rank
FROM class_grade T1
JOIN class_grade T2 ON T2.grade <= T1.grade
GROUP BY T1.grade
ORDER BY T1.grade