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

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

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

结果为：

步骤二： 按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