赞
踩
参考董旭阳TonyDong的博客,网址:https://blog.csdn.net/horses/article/details/86510905
lateral 横向的
mysql数据库从8.0支持横向派生表。
SELECT
语句中的其他表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 |
Copyright © 2003-2013 www.wpsshop.cn 版权所有,并保留所有权利。