SQL WITH语句详解|极客教程

SQL WITH语句详解

在SQL中，WITH语句通常被用来创建临时表，也称为公共表表达式（CTE）。这些临时表只在WITH语句中可见，可以被后续的SELECT、INSERT、UPDATE或DELETE语句引用。WITH语句让SQL查询更加简洁和易于理解，同时可以避免重复的逻辑。

语法

WITH语句的基本语法如下：

WITH cte_name AS (
    SELECT column1, column2, ...
    FROM table_name
    WHERE condition
)
SELECT *
FROM cte_name;

其中，cte_name是临时表的名称，可以在后续的SELECT语句中引用。WITH语句可以有多个公共表表达式，并且可以在后续的查询中进行联合操作。

示例

假设我们有一个名为”products”的表，记录了商品的信息，包括商品ID、名称、价格和类别。我们想要查询价格大于100的商品，并按类别进行分组。我们可以使用WITH语句来实现这一目的。

WITH expensive_products AS (
    SELECT *
    FROM products
    WHERE price > 100
)
SELECT category, COUNT(*) AS count
FROM expensive_products
GROUP BY category;

在上面的示例中，我们首先创建了一个名为”expensive_products”的临时表，其中包含价格大于100的商品记录。然后，我们在这个临时表上进行查询，统计每个类别中价格大于100的商品数量。

使用递归CTE

WITH语句还可以用于创建递归的公共表表达式（Recursive CTE），用来处理层次数据结构。递归CTE有两部分组成：基础查询和递归查询。

基础查询用于获取初始行，递归查询用于根据基础查询的结果生成更多行，直到满足停止条件。

下面是一个简单的递归CTE示例，假设我们有一个名为”employees”的表，记录了员工的ID和上级领导的ID。我们想要查询每个员工的直接上级领导，以及间接上级领导的链。

WITH recursive employee_hierarchy AS (
    SELECT id, name, manager_id, 1 as level
    FROM employees
    WHERE manager_id IS NULL
    UNION ALL
    SELECT e.id, e.name, e.manager_id, eh.level + 1
    FROM employees e
    JOIN employee_hierarchy eh
    ON e.manager_id = eh.id
)
SELECT *
FROM employee_hierarchy;

在上面的示例中，我们首先从employees表中选取顶层领导（manager_id为NULL的员工），作为初始行。然后通过递归查询，不断地将下一级员工和其直接上级领导的链添加到结果集中，直到所有员工的上下级关系都被遍历。