当前位置：首页 > article >正文

深入理解 SQL 中的 WITH AS 语法

article 2024/10/28 23:43:00

在日常数据库操作中，SQL 语句的复杂性往往会影响到查询的可读性和维护性。为了解决这个问题，Oracle 提供了 WITH AS 语法，这一功能可以极大地简化复杂查询，提升代码的清晰度。本文将详细介绍 WITH AS 的基本用法、优势以及一些实际应用示例。

1. 什么是 WITH AS

WITH AS 语法又称为公共表表达式（CTE，Common Table Expression），允许开发者在一个查询中定义一个或多个临时结果集，这些结果集可以在随后的主查询中被引用。通过这种方式，开发者可以将复杂的查询逻辑分解为更易于理解和维护的多个部分。
基本语法
基本的 WITH AS 语法结构如下：

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

2. 使用 WITH AS 的优势

2.1 提高可读性

复杂的 SQL 查询往往涉及多个嵌套的子查询，这不仅增加了代码的长度，还使得理解查询逻辑变得困难。通过 WITH AS，我们可以将逻辑划分为多个部分，每个部分清晰地命名，便于其他开发者或未来的自己快速理解。

2.2 避免重复计算

在一些复杂查询中，同一个计算可能会被多次调用。如果我们在每个地方都写相同的子查询，既浪费了资源，又降低了代码的可维护性。使用 WITH AS 可以只计算一次，然后在后续的查询中重用这个结果集。

2.3 递归查询支持

Oracle 的 WITH AS 还支持递归查询，这对于处理层级数据（如组织结构、文件系统等）非常有用。通过递归 CTE，开发者可以轻松地获取父子关系数据。

3. 实际应用示例

示例 1：计算平均工资

假设我们有一个员工表 employees，我们想找出工资高于 10000 的员工，并计算他们的平均工资：

WITH    employeesTemp AS (
    SELECT * FROM employees WHERE salary > 10000
)
SELECT AVG(salary) AS highSalary FROM employeesTemp;

在这个例子中，我们首先定义了一个临时表 employeesTemp，它包含所有工资超过 10000 的员工。随后，我们利用这个临时表计算这些员工的平均工资。

示例2：计算近10天特定时间段新增数据占比全天比例

WITH total_counts AS (
SELECT
    trunc(t.CREATED_TIME) AS DAY,
    count(1) AS total_count
FROM
    t_user t
WHERE
    t.CREATED_TIME > SYSDATE - 10
GROUP BY
    trunc(t.CREATED_TIME)
),
afternoon_counts AS (
SELECT
    trunc(t.CREATED_TIME) AS DAY,
    count(1) AS afternoon_count
FROM
    t_user t 
WHERE
    t.CREATED_TIME > SYSDATE - 10
    AND TO_CHAR(t.CREATED_TIME, 'HH24') BETWEEN '15' AND '17'
GROUP BY
    trunc(t.CREATED_TIME)
)
SELECT
    t.day,
    t.total_count,
    a.afternoon_count,
    ROUND(a.afternoon_count / t.total_count * 100, 2) AS percentage
FROM
    total_counts t
LEFT JOIN
    afternoon_counts a ON
    t.day = a.day
ORDER BY
    t.day;

在这个例子中，我们首先定义了临时表total_counts和afternoon_counts，其中afternoon_counts统计的是下午15～17点数据量，最后临时表total_counts和afternoon_counts关联查询，统计出近10天内15～17点数据量占比全天数据比例
在这里插入图片描述