本文关于（TiDB当中的公共表表达式-平凯星辰）。

公共表表达式 (CTE)

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

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

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

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

基本使用

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

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

非递归的 CTE

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

WITH <query_name> AS (    <query_definition>)SELECT ... FROM <query_name>;

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

• SQL

• Java

WITH top_50_eldest_authors_cte AS (    SELECT a.id, a.name, (IFNULL(a.death_year, YEAR(NOW())) - a.birth_year) AS age    FROM authors a    ORDER BY age DESC    LIMIT 50)SELECT    ANY_VALUE(ta.id) AS author_id,    ANY_VALUE(ta.age) AS author_age,    ANY_VALUE(ta.name) AS author_name,    COUNT(*) AS booksFROM top_50_eldest_authors_cte taLEFT JOIN book_authors ba ON ta.id = ba.author_idGROUP BY ta.id;

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

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

WITH books_authored_by_rm AS (    SELECT *    FROM books b    LEFT JOIN book_authors ba ON b.id = ba.book_id    WHERE author_id = 2299112019), books_with_average_ratings AS (    SELECT        b.id AS book_id,        AVG(r.score) AS average_rating    FROM books_authored_by_rm b    LEFT JOIN ratings r ON b.id = r.book_id    GROUP BY b.id), books_with_orders AS (    SELECT        b.id AS book_id,        COUNT(*) AS orders    FROM books_authored_by_rm b    LEFT JOIN orders o ON b.id = o.book_id    GROUP BY b.id)SELECT    b.id AS `book_id`,    b.title AS `book_title`,    br.average_rating AS `average_rating`,    bo.orders AS `orders`FROM    books_authored_by_rm b    LEFT JOIN books_with_average_ratings br ON b.id = br.book_id    LEFT JOIN books_with_orders bo ON b.id = bo.book_id;

查询结果如下：

+------------+-------------------------+----------------+--------+| book_id    | book_title              | average_rating | orders |+------------+-------------------------+----------------+--------+|  481008467 | The Documentary of goat |         2.0000 |     16 || 2224531102 | Brandt Skiles           |         2.7143 |     17 || 2641301356 | Sheridan Bashirian      |         2.4211 |     12 || 4154439164 | Karson Streich          |         2.5833 |     19 |+------------+-------------------------+----------------+--------+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

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

WITH RECURSIVE <query_name> AS (    <query_definition>)SELECT ... FROM <query_name>;

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

WITH RECURSIVE fibonacci (n, fib_n, next_fib_n) AS(  SELECT 1, 0, 1  UNION ALL  SELECT n + 1, next_fib_n, fib_n + next_fib_n FROM fibonacci WHERE n < 10)SELECT * FROM fibonacci;

查询结果如下：

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

上述就是小编为大家整理的（TiDB当中的公共表表达式-平凯星辰）
***

版权声明：本文内容由网络用户投稿，版权归原作者所有，本站不拥有其著作权，亦不承担相应法律责任。如果您发现本站中有涉嫌抄袭或描述失实的内容，请联系我们jiasou666@gmail.com 处理，核实后本网站将在24小时内删除侵权内容。