MySQL 中的 With 语法及使用方法（mysql中withas）

MySQL 中的 With 语法及使用方法

MySQL 中的 With 语法是一种用于创建临时表的语言元素。它允许用户为一个查询构建一个命名临时表，从而可以在一个大查询中使用该表，而无需查询多次相同的数据。

使用 With 语法有以下几步：

1.编写语法以构建临时表。

2.将该临时表与查询结合起来，以获得所需的结果。

以一个示例来说明：

WITH cte (col1, col2)

AS

(

SELECT SalesOrderID, ProductID

FROM Sales.SalesOrderDetl

)

SELECT ProductID, AVG(OrderQty) as avgorder

FROM cte

GROUP BY ProductID;

这个示例中，我们使用了一条 SQL 查询来计算每个产品的平均订单数量。该查询包含了两个列，一个是 ProductID，另一个是 avgorder。在这个示例中，我们使用了 WITH 子句来创建了一个临时的命名表 cte，来保存所有的 SalesOrderID 和 ProductID。我们然后将这个临时表与查询结合起来，以计算每个产品的平均订单数量。我们使用 GROUP BY 子句将结果按照产品 ID 进行分组。

需要注意的是，临时表只存在于查询的作用域中。如果您想使用多个查询来处理临时表，您应该在每个查询中都使用 WITH 子句来创建相同的临时表。

在实际应用中，WITH 语法常用于处理包含复杂联接的查询、递归查询以及需要计算聚合值的查询。以下是一个使用 WITH 语法来处理递归查询的示例代码：

WITH cte AS (

SELECT EmployeeID, EmployeeName, EmployeeParentID, 0 AS Level

FROM EmployeeTable

WHERE EmployeeParentID IS NULL

UNION ALL

SELECT EmployeeTable.EmployeeID, EmployeeTable.EmployeeName, EmployeeTable.EmployeeParentID, cte.Level + 1

FROM EmployeeTable

INNER JOIN cte ON cte.EmployeeID = EmployeeTable.EmployeeParentID

)

SELECT EmployeeID, EmployeeName, EmployeeParentID, Level

FROM cte

ORDER BY Level;

在这个示例中，我们使用 WITH 子句来创建一个递归的命名表 cte，来保存每个员工的姓名，ID 和其直接上级的 ID。我们使用了 UNION ALL 子句来连接不同的查询结果。在主查询中，我们使用了 ORDER BY 语句来按照 员工级别对查询结果进行排序。

总结

通过使用 WITH 语句，MySQL 可以更快地处理大量重复数据，从而提高查询效率。在常规查询下，使用 WITH 子句并非必须。但在需要处理大量重复数据的查询时，使用 WITH 语句可以大幅提高查询效率。

代码：

–CREATE TABLE Sales.SalesOrderDetl

–(

— SalesOrderDetlID int(11) NOT NULL AUTO_INCREMENT,

— SalesOrderID int(11) NOT NULL,

— UnitPrice decimal(8,2) NOT NULL,

— Discount decimal(8,2) NOT NULL,

— ProductID int(11) NOT NULL,

— OrderQty smallint(6) NOT NULL,

— PRIMARY KEY (SalesOrderDetlID)

–);

—

–CREATE TABLE EmployeeTable

–(

—EmployeeID INT PRIMARY KEY,

—EmployeeName VARCHAR(50) NOT NULL,

—EmployeeParentID INT

–);

–INSERT INTO EmployeeTable VALUES (1, ‘a’, NULL), (2, ‘b’, 1), (3, ‘c’, 1), (4, ‘d’, 2), (5, ‘e’, 2), (6, ‘f’, 5), (7, ‘g’, 6);

WITH cte (col1, col2)

AS

(

SELECT SalesOrderID, ProductID

FROM Sales.SalesOrderDetl

)

SELECT ProductID, AVG(OrderQty) as avgorder

FROM cte

GROUP BY ProductID;

WITH cte AS (

SELECT EmployeeID, EmployeeName, EmployeeParentID, 0 AS Level

FROM EmployeeTable

WHERE EmployeeParentID IS NULL

UNION ALL

SELECT EmployeeTable.EmployeeID, EmployeeTable.EmployeeName, EmployeeTable.EmployeeParentID, cte.Level + 1

FROM EmployeeTable

INNER JOIN cte ON cte.EmployeeID = EmployeeTable.EmployeeParentID

)

SELECT EmployeeID, EmployeeName, EmployeeParentID, Level

FROM cte

ORDER BY Level;