公共表表达式 (CTE)
- 基本使用
  - 非递归的 CTE
  - 递归的 CTE
- 扩展阅读

公共表表达式 (CTE)

由于业务的客观复杂性，有时候会写出长达 2000 行的单条 SQL 语句，其中包含大量的聚合和多层子查询嵌套，维护此类 SQL 堪称开发人员的噩梦。

在前面的小节当中已经介绍了如何使用视图简化查询，也介绍了如何使用临时表来缓存中间查询结果。

在这一小节当中，将介绍 TiDB 当中的公共表表达式（CTE）语法，它是一种更加便捷的复用查询结果的方法。

TiDB 从 5.1 版本开始支持 ANSI SQL 99 标准的 CTE 及其递归的写法，极大提升开发人员和 DBA 编写复杂业务逻辑 SQL 的效率，增强代码的可维护性。

基本使用

公共表表达式 (CTE) 是一个临时的中间结果集，能够在 SQL 语句中引用多次，提高 SQL 语句的可读性与执行效率。在 TiDB 中可以通过 WITH 语句使用公共表表达式。

公共表表达式可以分为非递归和递归两种类型。

非递归的 CTE

非递归的 CTE 使用如下语法进行定义：

1. WITH <query_name> AS (
2. <query_definition>
3. )
4. SELECT ... FROM <query_name>;

例如，假设还想知道最年长的 50 位作家分别编写过多少书籍。

SQL
Java

在 SQL 中，可以将临时表小节当中的例子改为以下 SQL 语句：

1. WITH top_50_eldest_authors_cte AS (
2. SELECT a.id, a.name, (IFNULL(a.death_year, YEAR(NOW())) - a.birth_year) AS age
3. FROM authors a
4. ORDER BY age DESC
5. LIMIT 50
6. )
7. SELECT
8. ANY_VALUE(ta.id) AS author_id,
9. ANY_VALUE(ta.age) AS author_age,
10. ANY_VALUE(ta.name) AS author_name,
11. COUNT(*) AS books
12. FROM top_50_eldest_authors_cte ta
13. LEFT JOIN book_authors ba ON ta.id = ba.author_id
14. GROUP BY ta.id;

查询结果如下：

1. +------------+------------+---------------------+-------+
2. | author_id  | author_age | author_name         | books |
3. +------------+------------+---------------------+-------+
4. | 1238393239 |         80 | Araceli Purdy       |     1 |
5. |  817764631 |         80 | Ivory Davis         |     3 |
6. | 3093759193 |         80 | Lysanne Harris      |     1 |
7. | 2299112019 |         80 | Ray Macejkovic      |     4 |
8. ...
9. +------------+------------+---------------------+-------+
10. 50 rows in set (0.01 sec)

在 Java 中的示例如下：

1. public List<Author> getTop50EldestAuthorInfoByCTE() throws SQLException {
2. List<Author> authors = new ArrayList<>();
3. try (Connection conn = ds.getConnection()) {
4. Statement stmt = conn.createStatement();
5. ResultSet rs = stmt.executeQuery("""
6. WITH top_50_eldest_authors_cte AS (
7. SELECT a.id, a.name, (IFNULL(a.death_year, YEAR(NOW())) - a.birth_year) AS age
8. FROM authors a
9. ORDER BY age DESC
10. LIMIT 50
11. )
12. SELECT
13. ANY_VALUE(ta.id) AS author_id,
14. ANY_VALUE(ta.name) AS author_name,
15. ANY_VALUE(ta.age) AS author_age,
16. COUNT(*) AS books
17. FROM top_50_eldest_authors_cte ta
18. LEFT JOIN book_authors ba ON ta.id = ba.author_id
19. GROUP BY ta.id;
20. """);
21. while (rs.next()) {
22. Author author = new Author();
23. author.setId(rs.getLong("author_id"));
24. author.setName(rs.getString("author_name"));
25. author.setAge(rs.getShort("author_age"));
26. author.setBooks(rs.getInt("books"));
27. authors.add(author);
28. }
29. }
30. return authors;
31. }

这时，可以发现名为 “Ray Macejkovic” 的作者写了 4 本书，继续通过 CTE 查询来了解这 4 本书的销量和评分：

1. WITH books_authored_by_rm AS (
2. SELECT *
3. FROM books b
4. LEFT JOIN book_authors ba ON b.id = ba.book_id
5. WHERE author_id = 2299112019
6. ), books_with_average_ratings AS (
7. SELECT
8. b.id AS book_id,
9. AVG(r.score) AS average_rating
10. FROM books_authored_by_rm b
11. LEFT JOIN ratings r ON b.id = r.book_id
12. GROUP BY b.id
13. ), books_with_orders AS (
14. SELECT
15. b.id AS book_id,
16. COUNT(*) AS orders
17. FROM books_authored_by_rm b
18. LEFT JOIN orders o ON b.id = o.book_id
19. GROUP BY b.id
20. )
21. SELECT
22. b.id AS `book_id`,
23. b.title AS `book_title`,
24. br.average_rating AS `average_rating`,
25. bo.orders AS `orders`
26. FROM
27. books_authored_by_rm b
28. LEFT JOIN books_with_average_ratings br ON b.id = br.book_id
29. LEFT JOIN books_with_orders bo ON b.id = bo.book_id
30. ;

查询结果如下：

1. +------------+-------------------------+----------------+--------+
2. | book_id    | book_title              | average_rating | orders |
3. +------------+-------------------------+----------------+--------+
4. |  481008467 | The Documentary of goat |         2.0000 |     16 |
5. | 2224531102 | Brandt Skiles           |         2.7143 |     17 |
6. | 2641301356 | Sheridan Bashirian      |         2.4211 |     12 |
7. | 4154439164 | Karson Streich          |         2.5833 |     19 |
8. +------------+-------------------------+----------------+--------+
9. 4 rows in set (0.06 sec)

在这个 SQL 语句，定义了三个 CTE 块，CTE 块之间使用 , 进行分隔。

先在 CTE 块 books_authored_by_rm 当中将该作者（作者 ID 为 2299112019）所编写的书查出来，然后在 books_with_average_ratings 和 books_with_orders 中分别查出这些书的平均评分和订单数，最后通过 JOIN 语句进行汇总。

值得注意的是，books_authored_by_rm 中的查询只会执行一次，TiDB 会开辟一块临时空间对查询的结果进行缓存，当 books_with_average_ratings 和 books_with_orders 引用时会直接从该临时空间当中获取数据。

小贴士

当默认的 CTE 查询执行效率不高时，你可以使用 MERGE() hint，将 CTE 子查询拓展到外部查询，以此提高执行效率。

递归的 CTE

递归的公共表表达式可以使用如下语法进行定义：

1. WITH RECURSIVE <query_name> AS (
2. <query_definition>
3. )
4. SELECT ... FROM <query_name>;

比较经典的例子是通过递归的 CTE 生成一组斐波那契数：

1. WITH RECURSIVE fibonacci (n, fib_n, next_fib_n) AS
2. (
3. SELECT 1, 0, 1
4. UNION ALL
5. SELECT n + 1, next_fib_n, fib_n + next_fib_n FROM fibonacci WHERE n < 10
6. )
7. SELECT * FROM fibonacci;

查询结果如下：

1. +------+-------+------------+
2. | n    | fib_n | next_fib_n |
3. +------+-------+------------+
4. |    1 |     0 |          1 |
5. |    2 |     1 |          1 |
6. |    3 |     1 |          2 |
7. |    4 |     2 |          3 |
8. |    5 |     3 |          5 |
9. |    6 |     5 |          8 |
10. |    7 |     8 |         13 |
11. |    8 |    13 |         21 |
12. |    9 |    21 |         34 |
13. |   10 |    34 |         55 |
14. +------+-------+------------+
15. 10 rows in set (0.00 sec)

扩展阅读

WITH