横向派生表

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

参数解释：

示例

创建测试表并添加测试数据

1. 创建表 students。

   CREATE TABLE students (
     id INT AUTO_INCREMENT PRIMARY KEY,
     name VARCHAR(50) NOT NULL,
     age INT
   );
   

2. 向表 students 中插入 3 条数据。

   INSERT INTO students (name, age) 
     VALUES ('name1', 20),
     ('name2', 22),
     ('name3', 21);
   

3. 创建表 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)
   );
   

4. 向表 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 的写法更为清晰和紧凑，易于理解和维护。

相关文档

• SELECT

• 查询中使用聚合函数