回忆是一座桥
却是通往寂寞的牢

SQL语法 - 公共表表达式(CTE)

公共表表达式(CTE)是一个临时的结果集,这些结果集可以在一个SELECT、INSERT、UPDATE或DELETE语句中被多次引用。CTE通常用于简化复杂的SQL查询,提高查询的可读性和维护性。

语法

CTE的语法如下:

WITH cte_name1 [(col_name1 [, col_name2] ...)] AS (subquery)
  [, cte_name2 [(col_name1 [, col_name2] ...)] AS (subquery)] ...
SELECT ... FROM cte_name ... -- 主查询

其中:

  • cte_name 是CTE的名称。

  • column1, column2, … 是CTE中列的名称(可选)。

  • 子查询部分定义了CTE的内容。

  • 主查询部分使用CTE进行进一步的查询操作。

示例

1、非递归CTE

假设有一个订单系统,包含 Orders OrderDetails Products 三个表,想要查询每个产品的总销售额。

WITH SalesCTE AS (
    SELECT ProductID, SUM(Quantity * UnitPrice) AS TotalSales
    FROM OrderDetails
    GROUP BY ProductID
),
ProductCTE AS (
    SELECT * FROM Products
)
SELECT p.ProductName, s.TotalSales
FROM ProductCTE p
JOIN SalesCTE s ON p.ProductID = s.ProductID;

2、递归CTE

CTE还可以用于递归查询,这在处理层次结构数据(如组织结构、分类目录等)时非常有用,比如查询一个公司的员工层级关系。

WITH EmployeeCTE AS ( -- 如果是在MySQL中,递归CTE需要使用`WITH RECURSIVE`语句
    -- 锚成员:选择CEO
    SELECT EmployeeID, ManagerID, EmployeeName
    FROM Employees
    WHERE ManagerID IS NULL
    UNION ALL
    -- 递归成员:选择下属员工
    SELECT e.EmployeeID, e.ManagerID, e.EmployeeName
    FROM Employees e
    INNER JOIN EmployeeCTE m ON e.ManagerID = m.EmployeeID
)
SELECT * FROM EmployeeCTE;

注意事项

1、性能考虑:虽然CTE可以提高查询的可读性,但在某些情况下可能会影响性能。特别是递归CTE,如果数据量较大,可能会导致查询变慢。

2、递归深度:递归CTE有默认的最大递归深度,超过这个深度会引发错误。

3、命名冲突:CTE的名称在查询中必须是唯一的,不能与其他表或CTE的名称冲突。

4、兼容性:虽然大多数现代数据库系统都支持CTE,但在一些旧版本的数据库系统中可能不支持。在使用前需要确认数据库系统的兼容性。

未经允许不得转载:夕枫 » SQL语法 - 公共表表达式(CTE)
订阅评论
提醒
guest
0 评论
内联反馈
查看所有评论