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

目录

• MySQL巧用表的自连接和运算符代替排序的几个例子
• 例1： SQL18
• 例2：SQL87

例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：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