在 Oracle SQL 中，WITH 子句用于定义一个或多个公共表表达式（CTE, Common Table Expression），然后可以在主查询中引用这些表达式。WITH 子句通常用于简化复杂查询的结构，增强可读性，并避免重复编写相同的子查询。

基本用法

WITH cte_name AS (
    -- 子查询，也相当于我们定义的一个数据集变量
    SELECT column1, column2
    FROM table_name
    WHERE conditions
)
-- 主查询
SELECT *
FROM cte_name
WHERE additional_conditions;

测试

假设我们有一个 employees 表，如下所示：

employee_id	first_name	last_name	salary	department_id
1	John	Doe	6000	10
2	Jane	Smith	8000	20
3	Jim	Brown	5500	10
4	Jake	White	7000	20
5	Jill	Black	6500	30

示例 1: 简单的 WITH 子句

查询每个部门的平均工资，然后查找那些平均工资高于 6000 的部门：

WITH department_salaries AS (
    SELECT department_id, AVG(salary) AS avg_salary
    FROM employees
    GROUP BY department_id
)
SELECT department_id, avg_salary
FROM department_salaries
WHERE avg_salary > 6000;

在这个示例中：

• department_salaries 是一个 CTE，用于计算每个部门的平均工资。 主查询从 CTE
• department_salaries 中选择那些平均工资高于 6000 的部门。

示例 2: 多个 CTE

假设我们需要计算每个部门的工资总和和平均工资，并筛选出总和大于 12000 的部门：

WITH total_salaries AS (
    SELECT department_id, SUM(salary) AS total_salary
    FROM employees
    GROUP BY department_id
),
average_salaries AS (
    SELECT department_id, AVG(salary) AS avg_salary
    FROM employees
    GROUP BY department_id
)
SELECT t.department_id, t.total_salary, a.avg_salary
FROM total_salaries t
JOIN average_salaries a ON t.department_id = a.department_id
WHERE t.total_salary > 12000;

在这个示例中：

• total_salaries CTE 计算每个部门的工资总和。
• average_salaries CTE 计算每个部门的平均工资。
• 主查询通过连接这两个 CTE，并筛选出工资总和大于 12000 的部门。

总结

1. 作用范围：WITH 子句定义的 CTE 仅在 WITH 子句后面的查询中有效。
2. 性能：CTE 通常被视为临时的视图，Oracle在执行查询时可能会优化 CTE 的使用方式，因此性能影响通常较小，但在某些复杂查询中，CTE 可能会影响性能。

通过合理使用 WITH子句，你可以简化复杂查询，提升 SQL 代码的可读性。

在 MyBatis XML 中使用 WITH 子句

1. 定义 SQL 映射：在 MyBatis 的 XML 文件中，你可以使用 标签来定义查询语句。你可以在查询中使用WITH 子句来定义一个或多个 CTE（公共表表达式）。
2. 创建映射文件：在你的 MyBatis XML 映射文件中，你可以像下面这样使用 WITH 子句。

示例

假设我们有一个 employees 表，并且我们想使用 WITH 子句来查询每个部门的平均工资和总工资。以下是 MyBatis XML 映射文件的一个示例：
XML 映射文件（EmployeeMapper.xml）

<mapper namespace="com.example.EmployeeMapper">

    <!-- 定义查询，使用 WITH 子句 -->
    <select id="selectDepartmentSalaries" resultType="map">
        <![CDATA[
        WITH department_salaries AS (
            SELECT department_id, AVG(salary) AS avg_salary
            FROM employees
            GROUP BY department_id
        )
        SELECT department_id, avg_salary
        FROM department_salaries
        WHERE avg_salary > #{minSalary}
        ]]>
    </select>

    <!-- 定义查询，使用多个 WITH 子句 -->
    <select id="selectTotalAndAverageSalaries" resultType="map">
        <![CDATA[
        WITH total_salaries AS (
            SELECT department_id, SUM(salary) AS total_salary
            FROM employees
            GROUP BY department_id
        ),
        average_salaries AS (
            SELECT department_id, AVG(salary) AS avg_salary
            FROM employees
            GROUP BY department_id
        )
        SELECT t.department_id, t.total_salary, a.avg_salary
        FROM total_salaries t
        JOIN average_salaries a ON t.department_id = a.department_id
        WHERE t.total_salary > #{minTotalSalary}
        ]]>
    </select>

</mapper>

1. <![CDATA[ ... ]]>：为了避免 XML 特殊字符（如 < 和 >）对 SQL 语句的干扰，通常会使用 CDATA 区块。这告诉 MyBatis 这个区域的内容是纯文本，不需要进行 XML 特殊字符的转义。
2. #{minSalary} 和 #{minTotalSalary}：这些是动态参数占位符，用于在执行查询时传递实际的参数值。
3. resultType=“map”：指定查询结果的返回类型为 map，你也可以根据实际需要返回其他类型，如 Java 对象等。

使用示例

在你的 Java 代码中，你可以调用这些映射的 SQL 查询，例如：

public interface EmployeeMapper {
    List<Map<String, Object>> selectDepartmentSalaries(@Param("minSalary") double minSalary);
    List<Map<String, Object>> selectTotalAndAverageSalaries(@Param("minTotalSalary") double minTotalSalary);
}

总结

在 MyBatis XML 文件中使用 WITH 子句是一种很好的方式来组织和简化复杂的 SQL 查询。通过合理使用 CTE，你可以使 SQL 查询更加清晰和易于维护。