横向派生表

横向派生表

更新时间：2026-08-13 19:27:47

本文将介绍横向派生表（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;