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

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;2024-08-13