oracle中with的用法是什么-猿码集

什么是with

Oracle中的with语句是使用SQL递归查询时非常有用的，它允许我们在查询中定义一个临时表，可以用于存储中间结果。因为with子句定义的临时表只在查询语句执行期间可用，所以它通常被称为公共表表达式或CTE。

with语法

with子句由两部分组成：

子查询名称或别名

查询语句

例如，下面是一个常规的查询，它使用WITH子句：


WITH temperature AS (
    SELECT city, avg_temp
    FROM weather
    WHERE month = 'January'
)
SELECT city, avg_temp
FROM temperature
WHERE avg_temp > 32;

在这个查询中，我定义了一个名为temperature的临时表，它包含每个城市在1月份的平均气温。然后我用温度表查询城市名称和平均气温，只有当平均气温大于32时才返回相应的城市。

with子句可以用来做什么

with子句有许多用途。

1. 递归查询

with语句在递归查询中非常有用。递归查询是一种查询技术，它在查询过程中重复查询同一个表，直到结果满足某个条件为止。

下面是一些使用WITH递归查询的示例：

示例1：查询部门的上下级关系

下面的递归查询可以用来查询一个给定部门及其下属部门的所有部门的名称。


WITH department_hierarchy (department_id, department_name, parent_department_id, level) AS (
    SELECT department_id, department_name, parent_department_id, 0
    FROM departments
    WHERE department_id = 1
    UNION ALL
    SELECT d.department_id, d.department_name, d.parent_department_id, level + 1
    FROM departments d
        JOIN department_hierarchy dh ON dh.department_id = d.parent_department_id
)
SELECT department_name, level
FROM department_hierarchy;

我们定义了一个名为department_hierarchy的临时表，用于保存部门及其下属部门的信息。我们从departments表中选取根部门，然后使用UNION ALL运算符将其与其子部门合并。这个过程一遍遍地重复，直到没有子节点为止。最后，我们查询这个表来获取部门名称和层级。

示例2：查询包含自引用的商品分类


WITH category_hierarchy (category_id, category_name, parent_category_id, level, path) AS (
    SELECT category_id, category_name, parent_category_id, 0, CAST(category_id AS VARCHAR2(100))
    FROM product_categories
    WHERE parent_category_id IS NULL
    UNION ALL
    SELECT pc.category_id, pc.category_name, pc.parent_category_id, ch.level + 1, ch.path || '_' || pc.category_id
    FROM product_categories pc
        JOIN category_hierarchy ch ON ch.category_id = pc.parent_category_id
)
SELECT category_name, level, path
FROM category_hierarchy
WHERE path LIKE '%_113%';

这个查询用来查找一个包含自引用的商品分类体系中包含某个子类别的所有类别。

2. 性能优化

with子句还可以用于优化查询性能。如果您有多个查询需要访问相同的公共数据集，那么可以使用with子句来避免多次扫描这个数据集。

示例3：使用公共表表达式提高查询性能


WITH customer_list AS (
    SELECT customer_id, customer_name, city, state
    FROM customers
    WHERE state = 'CA'
)
SELECT *
FROM orders o
    JOIN customer_list cl ON o.customer_id = cl.customer_id
WHERE o.order_date BETWEEN '01-JAN-20' AND '31-JAN-20';

在这个示例中，我使用一个名为customer_list的公共表表达式来存储所有来自加利福尼亚州的客户的信息。由于我们定义了这个公共表表达式，每个订单只需要与这个表关联一次，而不需要多次扫描整个customers表。

3. 优化查询可读性

with子句还可以用于优化查询的可读性。如果您有一个复杂的查询，其中有很多嵌套的子查询，那么可以使用with子句来重构这个查询，使它更易于阅读和理解。

示例4：使用公共表表达式简化复杂的查询


WITH summary_data AS (
    SELECT customer_id, 
        COUNT(order_id) AS num_orders, 
        SUM(total_amount) AS total_spent
    FROM orders
    GROUP BY customer_id
)
SELECT cl.customer_name, sd.num_orders, sd.total_spent
FROM summary_data sd
    JOIN customers cl ON sd.customer_id = cl.customer_id
WHERE sd.num_orders > 10 AND sd.total_spent > 1000;

在这个示例中，我使用一个名为summary_data的公共表表达式来生成一个摘要数据集，该数据集包含每个客户的订单数和总支出。我们然后将这个摘要数据集与customers表关联，以获取每个客户的姓名。最后，我们过滤掉那些订单数少于10个或支出少于1000美元的客户。

结论

with语句是Oracle中非常有用的一个功能。它提供了一种定义临时表的简单方法，可以用于存储中间结果。使用with语句可以让递归查询和查询优化更加方便，也可以使查询更加可读和易于理解。

oracle中with的用法是什么