横向派生表
本文将介绍横向派生表（Lateral Derived Table）的基本概念、使用限制以及提供相关示例。
横向派生表简介
派生表（Derived Table）是一个在 FROM 子句中使用的子查询，这个子查询产生的结果集临时作为一个表在外层查询中使用。派生表通常用于分组和聚合数据，或者创建满足特定条件的数据集，以便在主查询中进一步使用。
而横向派生表（Lateral Derived Table），则是一种特殊类型的派生表，它使用 LATERAL 关键字可以用来指定一个派生表，使其能够引用同一个 FROM 子句中先前定义的其他表或派生表的字段。这使得派生表中的子查询可以依赖于在同一 FROM 子句中定义的表，从而访问这些表的列值。
横向派生表的主要特点是它们可以引用在它们之前在 FROM 子句中定义的表的列，普通派生表不具备这种能力。
横向派生表（LATERAL 关键字）使用限制
LATERAL关键字必须使用在FROM子句中，它可以位于用逗号分隔的表列表中，或者是连接表达式中（如JOIN、INNER JOIN、CROSS JOIN、LEFT [OUTER] JOIN、RIGHT [OUTER] JOIN）。- 当
LATERAL位于JOIN语句中并引用左侧表的字段时，允许的连接类型包括INNER JOIN、CROSS JOIN和LEFT [OUTER] JOIN。 - 若
LATERAL位于JOIN语句中并引用右侧表的字段时，合法的连接操作为INNER JOIN、CROSS JOIN和RIGHT [OUTER] JOIN。 - 在横向派生表中引用聚合函数时，该聚合函数不能直接依赖于包含横向派生表的
FROM子句所在的外部查询。即横向派生表内部不能使用聚合函数直接引用外部查询结果。
使用 LATERAL 关键字用法
SELECT select_list
FROM table_name1,
LATERAL (SELECT select_list
FROM table_name2
WHERE table_name2.col_name = table_name1.col_name) AS lateral_derived_table_name
[...];
参数解释：
| 参数 | 说明 |
|---|---|
| select_list | 要检索的列的列表，可以是列名、表达式、聚合函数等。可以使用逗号分隔多个列。 |
| table_name1 | 要查询的主表。 |
| LATERAL | 用于指定一个横向关联子查询（横向派生表）。 |
| table_name2 | 从表，在 LATERAL 子查询中被引用，用于为每行 table_name1 数据提供相关的附加信息。 |
| lateral_derived_table_name | 从表别名，用于后续查询引用。 |
| [...] | 可选查询子句，例如 WHERE 子句等。 |
示例
创建测试表并添加测试数据
-
创建表
students。CREATE TABLE students (
id INT AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(50) NOT NULL,
age INT
); -
向表
students中插入 3 条数据。INSERT INTO students (name, age)
VALUES ('name1', 20),
('name2', 22),
('name3', 21); -
创建表
scores。CREATE TABLE scores (
id INT AUTO_INCREMENT PRIMARY KEY,
student_id INT,
subject VARCHAR(50) NOT NULL,
score DECIMAL(5, 2),
FOREIGN KEY (student_id) REFERENCES students(id)
); -
向表
scores中插入 9 条数据。INSERT INTO scores (student_id, subject, score)
VALUES (1, 'A', 86.5),
(1, 'B', 90.0),
(1, 'C', 91.5),
(2, 'A', 86.0),
(2, 'B', 92.0),
(2, 'C', 89.5),
(3, 'A', 93.0),
(3, 'B', 92.5),
(3, 'C', 85.0);
查询测试数据
从 students 和 scores 两个表中获取每个学生的姓名、平均分以及最高分。
Q1：使用多个子查询获取需求数据。对 scores 表进行了两次独立的分组聚合操作，扫描 scores 表两次分别计算平均分和最高分。然后通过 WHERE 子句将结果与 students 表进行连接。
SELECT st.name, sc.avg_score, scs.max_score
FROM students st,
(SELECT student_id, AVG(score) avg_score
FROM scores
GROUP BY student_id) sc,
(SELECT student_id, MAX(score) max_score
FROM scores
GROUP BY student_id) scs
WHERE sc.student_id = st.id
AND scs.student_id = st.id;
返回结果如下：
+-------+-----------+-----------+
| name | avg_score | max_score |
+-------+-----------+-----------+
| name1 | 89.333333 | 91.50 |
| name2 | 89.166667 | 92.00 |
| name3 | 90.166667 | 93.00 |
+-------+-----------+-----------+
3 rows in set
Q2：使用 LATERAL 关键字获取需求数据。使用 LATERAL 关键字（横向派生表），在一行 SQL 语句中同时计算每个学生的平均分和最高分，并且仅针对每个学生 ID 扫描一次 scores 表。
SELECT st.name, ld_tbl.avg_score, ld_tbl.max_score
FROM students st,
LATERAL (SELECT AVG(score) avg_score, MAX(score) max_score
FROM scores sc
WHERE sc.student_id = st.id) ld_tbl;
返回结果如下：
+-------+-----------+-----------+
| name | avg_score | max_score |
+-------+-----------+-----------+
| name1 | 89.333333 | 91.50 |
| name2 | 89.166667 | 92.00 |
| name3 | 90.166667 | 93.00 |
+-------+-----------+-----------+
3 rows in set
Q2 相比 Q1，在处理大型数据集时可能会有更高的性能，因为它避免了对 scores 表的重复扫描。Q2 的写法更为清晰和紧凑，易于理解和维护。