mysql cte 语法_MySQL CTE

MySQL CTE

简介：在本教程中，您将学习如何使用MySQL CTE或公用表表达式以更易读的方式构造复杂查询。

从版本8.0开始，MySQL简单地引入了公用表表达式功能或CTE，因此您应在计算机上安装MySQL 8.0，以便练习本教程中的语句。

什么是常见的公用表表达式或CTE

公共表表达式是只存在一个单一的SQL语句例如执行范围内的一个命名的临时结果集，如：SELECT，INSERT，UPDATE，或DELETE。

与派生表类似，CTE不作为对象存储，仅在执行查询期间持续存在。与派生表不同，CTE可以是自引用(递归CTE)，也可以在同一查询中多次引用。此外，与派生表相比，CTE提供了更好的可读性和性能。

MySQL CTE语法

CTE的结构包括名称，可选列列表和定义CTE的查询。CTE定义后，您可以使用它像一个视图SELECT，INSERT，UPDATE，DELETE，或CREATE VIEW语句。

以下是CTE的基本语法：

WITH cte_name (column_list) AS (

query

)

SELECT * FROM cte_name;

注意：查询中的列数必须与column_list中的列数相同。如果省略column_list，CTE将使用定义CTE的查询的列列表

简单的MySQL CTE示例

以下示例说明如何使用CTE从示例数据库中的customers表中查询数据。请注意，此示例仅用于演示目的，以便您轻松了解CTE概念。

WITH customers_in_usa AS (

SELECT

customerName, state

FROM

customers

WHERE

country = 'USA'

) SELECT

customerName

FROM

customers_in_usa

WHERE

state = 'CA'

ORDER BY customerName;

运行结果：

+------------------------------+

| customerName |

+------------------------------+

| Boards & Toys Co. |

| Collectable Mini Designs Co. |

| Corporate Gift Ideas Co. |

| Men 'R' US Retailers, Ltd. |

| Mini Gifts Distributors Ltd. |

| Mini Wheels Co. |

| Signal Collectibles Ltd. |

| Technics Stores Inc. |

| The Sharp Gifts Warehouse |

| Toys4GrownUps.com |

| West Coast Collectables Co. |

+------------------------------+

11 rows in set (0.25 sec)

在此示例中，CTE的名称是customers_in_usa，定义CTE的查询返回两列customerName和state。因此，customers_in_usaCTE返回位于美国的所有客户。

在定义customers_in_usaCTE之后，我们在SELECT语句中引用它同时查询“CA”的客户。

看另一个例子：

WITH topsales2013 AS (

SELECT

salesRepEmployeeNumber employeeNumber,

SUM(quantityOrdered * priceEach) sales

FROM

orders

INNER JOIN

orderdetails USING (orderNumber)

INNER JOIN

customers USING (customerNumber)

WHERE

YEAR(shippedDate) = 2013

AND status = 'Shipped'

GROUP BY salesRepEmployeeNumber

ORDER BY sales DESC

LIMIT 5

)

SELECT

employeeNumber, firstName, lastName, sales

FROM

employees

JOIN

topsales2013 USING (employeeNumber);

运行结果：

+----------------+-----------+-----------+-----------+

| employeeNumber | firstName | lastName | sales |

+----------------+-----------+-----------+-----------+

| 1165 | Leslie | Jennings | 413219.85 |

| 1370 | Gerard | Hernandez | 295246.44 |

| 1401 | Pamela | Castillo | 289982.88 |

| 1621 | Mami | Nishi | 267249.40 |

| 1501 | Larry | Bott | 261536.95 |

+----------------+-----------+-----------+-----------+

5 rows in set (0.17 sec)

在此示例中，CTE在2013年返回前5名销售代表。之后，我们引用topsales2013CTE以获取有关销售代表的其他信息，包括名字和姓氏。

MySQL CTE高级实例

请参阅以下示例：

WITH salesrep AS (

SELECT

employeeNumber,

CONCAT(firstName, ' ', lastName) AS salesrepName

FROM

employees

WHERE

jobTitle = 'Sales Rep'

),

customer_salesrep AS (

SELECT

customerName, salesrepName

FROM

customers

INNER JOIN

salesrep ON employeeNumber = salesrepEmployeeNumber

)

SELECT

*

FROM

customer_salesrep

ORDER BY customerName;

运行结果：

+------------------------------------+------------------+

| customerName | salesrepName |

+------------------------------------+------------------+

| Alpha Cognac | Gerard Hernandez |

| Amica Models & Co. | Pamela Castillo |

| Anna's Decorations, Ltd | Andy Fixter |

| Atelier graphique | Gerard Hernandez |

| Australian Collectables, Ltd | Andy Fixter |

| Australian Collectors, Co. | Andy Fixter |

| Australian Gift Network, Co | Andy Fixter |

| Auto Associs & Cie. | Gerard Hernandez |

...

在此示例中，我们在同一查询中有两个CTE。第一个CTE(  salesrep)获得职称为销售代表的员工。第二个CTE(customer_salesrep)参考INNER JOIN 子句中的第一个CTE，以获得销售代表和每个销售代表负责的客户。

在进行第二次CTE之后，我们使用SELECT带有ORDER BY子句的简单语句从CTE查询数据。

WITH子句用法

有一些上下文可以使用WITH子句来创建公用表表达式：

首先，WITH子句可以在开始时使用SELECT，UPDATE和DELETE语句：

WITH ... SELECT ...

WITH ... UPDATE ...

WITH ... DELETE ...

其次，WITH子句可以在子查询或派生表子查询的开头使用：

SELECT ... WHERE id IN (WITH ... SELECT ...);

SELECT * FROM (WITH ... SELECT ...) AS derived_table;

第三，WITH可以在SELECT包含SELECT子句的语句之前使用子句：

CREATE TABLE ... WITH ... SELECT ...

CREATE VIEW ... WITH ... SELECT ...

INSERT ... WITH ... SELECT ...

REPLACE ... WITH ... SELECT ...

DECLARE CURSOR ... WITH ... SELECT ...

EXPLAIN ... WITH ... SELECT ...

在本教程中，您学习了如何使用MySQL CTE构建复杂查询。