oracle with 递归查询
在Oracle SQL中，WITH子句（也称为公共表达式或CTE，Common Table Expressions）用于定义临时的命名结果集，这些结果集可以在主查询中引用。WITH子句可以简化复杂的SQL查询，使其更具可读性，特别是当涉及到多个子查询或递归查询时。
以下是WITH子句的基本语法：
sql复制代码
WITH cte_name (column1, column2, ...) AS ( | |
-- CTE的定义，通常是一个SELECT语句 | |
) | |
SELECT * FROM cte_name; -- 主查询，引用CTE |
示例：使用WITH子句简化查询
假设我们有一个orders表，它包含订单信息，以及一个order_items表，它包含订单项的信息。我们想要查询每个订单的总金额。我们可以使用WITH子句来首先计算每个订单项的总金额，然后在主查询中引用这个计算结果。
sql复制代码
WITH OrderSums AS ( | |
SELECT order_id, SUM(item_price * quantity) AS total_amount | |
FROM order_items | |
GROUP BY order_id | |
) | |
SELECT o.order_id, o.order_date, os.total_amount | |
FROM orders o | |
JOIN OrderSums os ON o.order_id = os.order_id; |
在这个例子中，我们定义了一个名为OrderSums的CTE，它计算了每个订单的总金额。然后，在主查询中，我们将orders表与OrderSums CTE连接起来，以获取每个订单的总金额。
递归查询中的WITH子句
WITH子句特别适用于递归查询，因为它允许你引用自身来构建层次结构或执行其他需要递归的操作。
例如，假设你有一个表示组织结构的表，其中每个员工都有一个上级。你可以使用递归的WITH子句来查找某个员工的所有下级：
sql复制代码
WITH RECURSIVE Subordinates AS ( | |
-- 基础情况：直接下级 | |
SELECT employee_id, manager_id, name | |
FROM employees | |
WHERE manager_id = :some_manager_id | |
UNION ALL | |
-- 递归情况：下级的下级 | |
SELECT e.employee_id, e.manager_id, e.name | |
FROM employees e | |
JOIN Subordinates s ON e.manager_id = s.employee_id | |
) | |
SELECT * FROM Subordinates; |
在这个递归查询中，我们首先定义了一个名为Subordinates的CTE，它首先选择直接属于某个特定经理的下级员工（基础情况），然后递归地选择这些下级的下级（递归情况）。通过这种方式，我们可以获取整个下级员工层次结构。
通过使用WITH子句，你可以编写更加模块化和可维护的SQL代码，特别是当处理复杂的查询逻辑时。