LATERAL横向派生表

参考董旭阳TonyDong的博客，网址：https://blog.csdn.net/horses/article/details/86510905

 

lateral 横向的

mysql数据库从8.0支持横向派生表。

• 派生表不能引用它所在的SELECT语句中的其他表
• 在 MySQL 8.0.14 之前，派生表不能引用它所在的SELECT语句外部的表

 

简单来说，就是派生表必须能够单独运行，而不能依赖其他表。

示例表和数据：

表：工作表（jobs）、员工表(employees)、和部门表(departments)。

查找每个部门中薪水最高的 Top 5 和对应的员工？

SELECT d.department_name,
       (SELECT e.salary
          FROM employees e
         WHERE e.department_id = d.department_id
         ORDER BY e.salary DESC LIMIT 5
       )
  FROM departments d;
ERROR 1242 (21000): Subquery returns more than 1 row
以上查询失败的原因在于SELECT子查询只能返回 1 条数据。

SELECT d.department_name, t.first_name, t.last_name, t.salary
  FROM departments d
  LEFT JOIN (SELECT e.department_id, e.first_name, e.last_name, e.salary
               FROM employees e
              WHERE e.department_id = d.department_id
              ORDER BY e.salary DESC LIMIT 5) t
    ON d.department_id = t.department_id 
 ORDER BY d.department_name, t.salary DESC;
ERROR 1054 (42S22): Unknown column 'd.department_id' in 'where clause'
以上语句失败的原因在于子查询 t 不能引用外部查询中的 departments 表。

 

查找每个部门中薪水最高的 Top 5 和对应的员工？

SELECT d.department_name, t.first_name, t.last_name, t.salary
  FROM departments d
  LEFT JOIN LATERAL (SELECT e.department_id, e.first_name, e.last_name, e.salary
               FROM employees e
              WHERE e.department_id = d.department_id
              ORDER BY e.salary DESC LIMIT 5) t
    ON d.department_id = t.department_id 
 ORDER BY d.department_name, t.salary DESC;
 

+----------------------+-------------+-----------+----------+------+
| department_name      | first_name  | last_name | salary   | rn   |
+----------------------+-------------+-----------+----------+------+
| Accounting           | Shelley     | Higgins   | 12008.00 |    1 |
| Accounting           | William     | Gietz     |  8300.00 |    2 |
| Administration       | Jennifer    | Whalen    |  4400.00 |    1 |
| Benefits             | NULL        | NULL      |     NULL | NULL |
| Construction         | NULL        | NULL      |     NULL | NULL |
| Contracting          | NULL        | NULL      |     NULL | NULL |