在数据库查询中，子查询和公用表表达式 (CTE) 是两种强大的工具，它们允许我们创建更加复杂和模块化的查询。下面我们将详细探讨这两种技术，并通过实例来说明它们的使用。

子查询

子查询，也称为内嵌查询或内查询，是嵌入在其他 SQL 查询中的查询。子查询可以出现在 SELECT, FROM, WHERE 或 HAVING 子句中，并且它们可以返回标量值、单列、单行或多行结果集。

实例数据表

假设我们有以下两个数据表：

Employees 表

EmployeeID	EmployeeName	DepartmentID	Salary
1	Alice	1	70000
2	Bob	2	60000
3	Charlie	1	80000
4	David	3	50000

表结构

SQL
CREATE TABLE Employees (
    EmployeeID INT PRIMARY KEY,
    EmployeeName VARCHAR(255),
    DepartmentID INT,
    Salary DECIMAL(10, 2) -- Assuming a maximum salary of 99999999.99
);

测试数据

SQL
INSERT INTO Employees (EmployeeID, EmployeeName, DepartmentID, Salary) VALUES
(1, 'Alice', 1, 70000),
(2, 'Bob', 2, 60000),
(3, 'Charlie', 1, 80000),
(4, 'David', 3, 50000);

Departments 表

DepartmentID	DepartmentName
1	HR
2	IT
3	Sales

表结构

SQL
CREATE TABLE Departments (
    DepartmentID INT PRIMARY KEY,
    DepartmentName VARCHAR(255)
);

测试数据

SQL
INSERT INTO Departments (DepartmentID, DepartmentName) VALUES
(1, 'HR'),
(2, 'IT'),
(3, 'Sales');

子查询示例

假设我们想要找出薪水高于部门平均薪水的员工。我们可以使用子查询来实现这个目标。

SQL
SELECT EmployeeName, Salary
FROM Employees
WHERE Salary > (
    SELECT AVG(Salary)
    FROM Employees
    WHERE DepartmentID = Employees.DepartmentID
);

在这个例子中，子查询计算了与外层查询中相同部门的员工的平均薪水。然后，外层查询使用这个结果来找出薪水高于平均值的员工。

公用表表达式 (CTE)

公用表表达式 (CTE) 提供了一种将查询的结果集命名并在后续查询中引用这个命名结果集的方法。CTE 使得查询更加易读和维护，特别是在执行复杂的查询，如递归查询时。

CTE 示例

假设我们现在想要生成一个报告，列出每个部门的平均薪水。我们可以使用 CTE 来简化这个查询。

SQL
WITH DepartmentAverages AS (
    SELECT DepartmentID, AVG(Salary) AS AverageSalary
    FROM Employees
    GROUP BY DepartmentID
)
SELECT d.DepartmentName, da.AverageSalary
FROM DepartmentAverages da
JOIN Departments d ON da.DepartmentID = d.DepartmentID;

在这个例子中，DepartmentAverages 是一个 CTE，它包含了每个部门的平均薪水。然后我们在主查询中引用了这个 CTE，并与 Departments 表进行了连接以获取部门名称。

CTE 递归

当你想要将一列中的数量拆分成多个单独的行时，你可以使用递归公用表表达式（CTE）来实现。下面是一个例子，假设我们有一个 Orders 表，其中包含 OrderID 和 Quantity 列，我们想要将每个订单的数量拆分成多个单一数量的行。

Orders 表

OrderID	Quantity
1	3
2	1
3	2

表结构

SQL
CREATE TABLE Orders (
    OrderID INT PRIMARY KEY,
    Quantity INT
);

测试数据

SQL
INSERT INTO Orders (OrderID, Quantity) VALUES
(1, 3),
(2, 1),
(3, 2);

我们想要得到的结果是：

OrderID	Quantity
1	1
1	1
1	1
2	1
3	1
3	1

下面是使用递归 CTE 实现上述要求的 SQL 示例：

SQL
WITH CTE AS (
  SELECT OrderID, Quantity
  FROM Orders
  WHERE Quantity > 0
  UNION ALL
  SELECT OrderID, Quantity - 1
  FROM CTE
  WHERE Quantity > 1
)
SELECT OrderID, 1 AS Quantity
FROM CTE
ORDER BY OrderID;

在这个递归 CTE 中，我们首先选择所有订单和数量，其中数量大于 0。然后，我们递归地从每个订单的数量中减去 1，直到数量为 1。在最终的 SELECT 语句中，我们简单地选择 OrderID 和数量 1 作为结果。

子查询与 CTE 的对比

子查询和 CTE 都可以用来简化复杂的查询，但它们之间有一些关键的差异：

• 可读性：CTE 通常更易于阅读和维护，特别是在涉及多个步骤或递归查询的情况下。
• 性能：在某些数据库系统中，CTE 可以提高性能，因为它们允许查询优化器更好地理解查询的结构。然而，这并不总是情况，有时子查询可能更高效。
• 作用域：子查询的作用域限于包含它们的查询，而 CTE 在定义后可以在整个查询中多次引用。

总结

子查询和 CTE 都是数据库查询中不可或缺的工具。子查询非常适合于单个的、简单的内嵌查询，而 CTE 则更适合于复杂的、多步骤的查询和需要重复引用的情况。理解何时使用子查询和 CTE，以及如何有效地利用它们，对于编写高效和可维护的 SQL 查询至关重要。