9、编写高级查询：掌握SQL Server 2008的CTE和高级查询技巧

编写高级查询：掌握SQL Server 2008的CTE和高级查询技巧

1. 引言

在SQL Server 2008中，T-SQL语言引入了许多新特性和增强功能，使得编写复杂的查询变得更加容易和高效。特别是公用表表达式（Common Table Expressions, CTE）和窗口函数（Window Functions）等特性，极大地提升了SQL查询的能力。本文将详细介绍如何使用这些高级功能，帮助你在实际工作中更高效地处理和分析数据。

2. 使用公用表表达式（CTE）

公用表表达式（CTE）是一种临时结果集，可以在查询中重复使用。CTE不仅可以简化复杂的查询逻辑，还可以提高查询的可读性和维护性。下面我们通过具体的例子来展示CTE的使用方法。

2.1 单个CTE的使用

CTE的基本语法如下：

WITH cte_name AS (
    SELECT column1, column2, ...
    FROM table_name
)
SELECT *
FROM cte_name;

示例：查询客户订单详情

假设我们有一个名为 Sales.SalesOrderHeader 的表，包含客户的订单信息。我们可以使用CTE来简化查询：

WITH orders AS (
    SELECT SalesOrderID, CustomerID
    FROM Sales.SalesOrderHeader
)
SELECT c.CustomerID, orders.SalesOrderID
FROM Sales.Customer AS c
INNER JOIN orders ON c.CustomerID = orders.CustomerID;

2.2 多个CTE的使用

你还可以在同一查询中定义多个CTE，以进一步简化复杂的查询逻辑。多个CTE的语法如下：

WITH cte_name1 AS (
    SELECT column1, column2, ...
    FROM table_name1
),
cte_name2 AS (
    SELECT column1, column2, ...
    FROM table_name2
)
SELECT *
FROM cte_name1
JOIN cte_name2 ON cte_name1.column = cte_name2.column;

示例：查询客户订单和销售总额

WITH customer_sales AS (
    SELECT CustomerID, SUM(TotalDue) AS TotalSales
    FROM Sales.SalesOrderHeader
    GROUP BY CustomerID
),
average_sales AS (
    SELECT AVG(TotalSales) AS AvgSales
    FROM customer_sales
)
SELECT cs.CustomerID, cs.TotalSales, a.AvgSales
FROM customer_sales AS cs, average_sales AS a
WHERE cs.TotalSales > a.AvgSales;

3. 创建递归CTE

递归CTE允许你在一个CTE中调用自身，从而实现递归查询。这对于处理层次结构数据（如组织结构图）非常有用。递归CTE由两部分组成：锚点成员和递归成员。

3.1 锚点成员

锚点成员是递归查询的起点，通常表示层级结构的根节点。例如，在组织结构图中，锚点成员通常是CEO的记录。

3.2 递归成员

递归成员通过将CTE与原始表连接，逐步构建层级结构。递归成员会不断调用自身，直到没有更多的记录可以处理。

示例：构建组织结构图

WITH OrgChart(EmployeeID, ManagerID, Title, Level, Node) AS (
    -- 锚点成员
    SELECT EmployeeID, ManagerID, Title, 0 AS Level, CONVERT(VARCHAR(30), '/') AS Node
    FROM HumanResources.Employee
    WHERE ManagerID IS NULL
    UNION ALL
    -- 递归成员
    SELECT a.EmployeeID, a.ManagerID, a.Title, b.Level + 1, 
           CONVERT(VARCHAR(30), b.Node + CONVERT(VARCHAR, a.ManagerID) + '/')
    FROM HumanResources.Employee AS a
    INNER JOIN OrgChart AS b ON a.ManagerID = b.EmployeeID
)
SELECT EmployeeID, ManagerID, SPACE(Level * 3) + Title AS Title, Level, Node
FROM OrgChart
ORDER BY Node;

3.3 递归查询的注意事项

• 默认情况下，递归查询最多执行100次。如果需要调整递归次数，可以使用 OPTION(MAXRECURSION n) 选项。
• 如果递归查询编写不当，可能会导致无限循环。为了避免这种情况，确保递归成员最终会终止。

4. 使用窗口函数

窗口函数允许你在查询中对数据进行分组和排序，而不影响原始表的结构。常用的窗口函数包括 ROW_NUMBER() 、 RANK() 、 DENSE_RANK() 和 NTILE() 等。这些函数特别适用于需要对数据进行排名或分组的场景。

4.1 使用 NTILE 进行分组

NTILE 函数将结果集划分为指定数量的组（或桶），并为每一行分配一个组号。例如，你可以使用 NTILE 来根据销售人员的业绩分配奖金。

示例：根据销售额分配奖金

SELECT SalesPersonID, SUM(TotalDue) AS TotalSales,
       NTILE(10) OVER (ORDER BY SUM(TotalDue)) * 10000 / COUNT(*) OVER () AS Bonus
FROM Sales.SalesOrderHeader
WHERE SalesPersonID IS NOT NULL AND OrderDate BETWEEN '1/1/2004' AND '12/31/2004'
GROUP BY SalesPersonID
ORDER BY TotalSales;

SalesPersonID	TotalSales	Bonus
274	500000	5000
275	450000	4500
276	400000	4000

4.2 使用 ROW_NUMBER 进行排名

ROW_NUMBER 函数为每一行分配唯一的行号，适用于需要对结果进行排序和排名的场景。

示例：为每个销售人员分配排名

WITH ranked_sales AS (
    SELECT SalesPersonID, SUM(TotalDue) AS TotalSales,
           ROW_NUMBER() OVER (ORDER BY SUM(TotalDue) DESC) AS Rank
    FROM Sales.SalesOrderHeader
    WHERE SalesPersonID IS NOT NULL AND OrderDate BETWEEN '1/1/2004' AND '12/31/2004'
    GROUP BY SalesPersonID
)
SELECT SalesPersonID, TotalSales, Rank
FROM ranked_sales;

SalesPersonID	TotalSales	Rank
274	500000	1
275	450000	2
276	400000	3

---

在实际工作中，CTE和窗口函数是非常强大的工具，可以帮助你更高效地处理和分析数据。通过理解和掌握这些高级查询技巧，你将能够编写出更加简洁、高效的SQL查询。接下来的部分将继续探讨更多高级查询技术和应用场景。

5. 高级CTE应用

除了基本的CTE和递归CTE，CTE还有许多高级应用，可以进一步简化复杂的查询逻辑。下面我们将介绍一些常见的高级CTE应用。

5.1 在CTE中调用其他CTE

你可以在一个CTE中调用另一个CTE，从而实现更复杂的查询逻辑。例如，假设我们需要查询员工的工作历史记录，并且希望在查询中包含一些额外的计算字段。

示例：查询员工的工作历史记录

WITH cte1 AS (
    SELECT EmployeeID, EffDate, EffSeq, EmploymentStatus, JobTitle, Salary, ActionDesc
    FROM JobHistory
),
cte2 AS (
    SELECT EmployeeID, MAX(EffDate) AS MaxEffDate
    FROM cte1
    GROUP BY EmployeeID
),
final_query AS (
    SELECT c1.EmployeeID, c1.EffDate, c1.EffSeq, c1.EmploymentStatus, c1.JobTitle, c1.Salary, c1.ActionDesc
    FROM cte1 AS c1
    INNER JOIN cte2 AS c2 ON c1.EmployeeID = c2.EmployeeID AND c1.EffDate = c2.MaxEffDate
)
SELECT *
FROM final_query;

5.2 使用CTE处理复杂业务逻辑

CTE还可以用于处理复杂的业务逻辑，例如计算员工的有效工作记录。通过CTE，你可以逐步构建查询逻辑，使其更易于理解和维护。

示例：计算员工在特定日期的有效工作记录

WITH EffectiveDate AS (
    SELECT EmployeeID, MAX(EffDate) AS MaxEffDate
    FROM JobHistory
    WHERE EffDate <= @Date
    GROUP BY EmployeeID
),
EffectiveSeq AS (
    SELECT EmployeeID, MAX(EffSeq) AS MaxEffSeq
    FROM JobHistory
    WHERE EffDate = (SELECT MaxEffDate FROM EffectiveDate WHERE EmployeeID = JobHistory.EmployeeID)
    GROUP BY EmployeeID
)
SELECT j.EmployeeID, j.EffDate, j.EffSeq, j.EmploymentStatus, j.JobTitle, j.Salary, j.ActionDesc
FROM JobHistory AS j
INNER JOIN EffectiveSeq AS es ON j.EmployeeID = es.EmployeeID AND j.EffSeq = es.MaxEffSeq
ORDER BY j.EmployeeID;

6. 数据操纵语句中的 OUTPUT 子句

OUTPUT 子句是SQL Server 2005引入的一个新特性，允许你在执行数据操纵语句（如 INSERT 、 UPDATE 、 DELETE ）时返回或存储受影响的数据。这对于需要跟踪数据变化的场景非常有用。

6.1 使用 OUTPUT 子句返回受影响的数据

示例：返回插入操作后的数据

DECLARE @InsertedData TABLE (EmployeeID INT, FirstName NVARCHAR(50), LastName NVARCHAR(50));

INSERT INTO Employees (FirstName, LastName)
OUTPUT INSERTED.EmployeeID, INSERTED.FirstName, INSERTED.LastName INTO @InsertedData
VALUES ('John', 'Doe');

SELECT * FROM @InsertedData;

6.2 使用 OUTPUT 子句保存受影响的数据

示例：保存更新操作后的数据

DECLARE @UpdatedData TABLE (EmployeeID INT, OldSalary DECIMAL(10, 2), NewSalary DECIMAL(10, 2));

UPDATE Employees
SET Salary = Salary * 1.1
OUTPUT DELETED.EmployeeID, DELETED.Salary AS OldSalary, INSERTED.Salary AS NewSalary INTO @UpdatedData
WHERE DepartmentID = 1;

SELECT * FROM @UpdatedData;

7. 使用 MERGE 语句进行数据同步

MERGE 语句是SQL Server 2008引入的一个强大功能，允许你在一个语句中完成插入、更新和删除操作。这对于需要同步源表和目标表的数据非常有用。

7.1 MERGE 语句的基本语法

MERGE INTO TargetTable AS target
USING SourceTable AS source
ON target.KeyColumn = source.KeyColumn
WHEN MATCHED THEN
    UPDATE SET target.Column1 = source.Column1, target.Column2 = source.Column2
WHEN NOT MATCHED BY TARGET THEN
    INSERT (Column1, Column2)
    VALUES (source.Column1, source.Column2)
WHEN NOT MATCHED BY SOURCE THEN
    DELETE;

示例：同步客户数据

MERGE INTO Customers AS target
USING CustomerSource AS source
ON target.CustomerID = source.CustomerID
WHEN MATCHED THEN
    UPDATE SET target.FirstName = source.FirstName, target.LastName = source.LastName
WHEN NOT MATCHED BY TARGET THEN
    INSERT (CustomerID, FirstName, LastName)
    VALUES (source.CustomerID, source.FirstName, source.LastName)
WHEN NOT MATCHED BY SOURCE THEN
    DELETE;

8. 总结

通过学习和掌握CTE、窗口函数、 OUTPUT 子句和 MERGE 语句等高级查询技巧，你将能够更高效地处理和分析数据。这些功能不仅简化了复杂的查询逻辑，还提高了查询的可读性和维护性。在实际工作中，合理运用这些技术可以显著提升工作效率。

关键点回顾

• CTE ：简化复杂查询逻辑，提高查询可读性和维护性。
• 递归CTE ：处理层次结构数据，如组织结构图。
• 窗口函数 ：对数据进行分组和排序，适用于排名和分组场景。
• OUTPUT 子句 ：返回或保存数据操纵语句中受影响的数据。
• MERGE 语句 ：在一个语句中完成插入、更新和删除操作，适用于数据同步场景。

下一步学习建议

为了进一步提升你的SQL技能，建议你继续探索SQL Server的其他高级特性，如全文搜索、地理空间数据处理等。同时，多实践和总结经验，不断优化查询性能，以应对日益复杂的业务需求。

---

通过以上内容的学习，相信你已经掌握了编写高级查询的核心技巧。在实际工作中，灵活运用这些技术将使你能够更高效地处理和分析数据，为你的职业生涯带来更多机会。希望这篇文章能为你提供有价值的参考和指导。