SqlServer 2005 T-SQL Query 学习笔记（4）

SqlServer 2005 T-SQL Query 学习笔记（4）

本文将介绍 SqlServer 2005 T-SQL Query 的一些高级用法，包括使用 WITH 语句、使用 PIVOT 和 UNPIVOT 进行数据透视和反透视、使用 CTE（公共表表达式）等。

使用 WITH 语句

WITH 语句是 SqlServer 2005 引入的一种新语法，它可以让我们在查询中定义一个临时的结果集，然后在后续的查询中使用这个结果集。WITH 语句的语法如下：

WITH CTE_Name (Column1, Column2, ...) AS (
    SELECT Column1, Column2, ...
    FROM Table1
    WHERE ...
)
SELECT ...
FROM CTE_Name
WHERE ...

其中，CTE_Name 是公共表表达式的名称，Column1、Column2 等是公共表表达式的列名，SELECT 子句是公共表表达式的查询语句。在后续的查询中，我们可以使用 CTE_Name 来引用这个公共表表达式。

以下是一个示例说明，演示了如何使用 WITH 语句：

WITH Sales_CTE (Region, TotalSales) AS (
    SELECT Region, SUM(SalesAmount)
    FROM Sales
    GROUP BY Region
)
SELECT Region, TotalSales
FROM Sales_CTE
WHERE TotalSales > 1000000

在这个示例中，我们使用 WITH 语句定义了一个名为 Sales_CTE 的公共表表达式，它计算了每个地区的销售总额。然后，在后续的查询中，我们使用 Sales_CTE 来筛选销售总额大于 1000000 的地区。

使用 PIVOT 和 UNPIVOT 进行数据透视和反透视

PIVOT 和 UNPIVOT 是 SqlServer 2005 引入的两个新语法，它们可以让我们在查询中进行数据透视和反透视。数据透视是指将行数据转换为列数据，数据反透视是指将列数据转换为行数据。

使用 PIVOT 进行数据透视

PIVOT 语法如下：

SELECT ...
FROM Table1
PIVOT (
    AggregateFunction(Column2)
    FOR Column1 IN (Value1, Value2, ...)
) AS P

其中，AggregateFunction 是聚合函数，Column1 是要透视的列，Column2 是要聚合的列，Value1、Value2 等是透视后的列名。在透视后的结果集中，每个 Value1、Value2 等都是一个新的列，它们的值是 Column2 经过 AggregateFunction 聚合后的结果。

以下是一个示例说明，演示了如何使用 PIVOT 进行数据透视：

SELECT *
FROM (
    SELECT Region, Product, SalesAmount
    FROM Sales
) AS S
PIVOT (
    SUM(SalesAmount)
    FOR Product IN (Product1, Product2, Product3)
) AS P

在这个示例中，我们使用 PIVOT 将 Sales 表中的行数据透视为列数据。透视后的结果集中，每个 Product1、Product2、Product3 都是一个新的列，它们的值是 SalesAmount 经过 SUM 聚合后的结果。

使用 UNPIVOT 进行数据反透视

UNPIVOT 语法如下：

SELECT ...
FROM Table1
UNPIVOT (
    Column2 FOR Column1 IN (Value1, Value2, ...)
) AS U

其中，Column1 是要反透视的列，Value1、Value2 等是要反透视的列名，Column2 是要转换为行数据的列。在反透视后的结果集中，每个 Value1、Value2 等都是一个新的行，它们的值是 Column2。

以下是一个示例说明，演示了如何使用 UNPIVOT 进行数据反透视：

SELECT Region, Product, SalesAmount
FROM (
    SELECT *
    FROM Sales
) AS S
UNPIVOT (
    SalesAmount FOR Product IN (Product1, Product2, Product3)
) AS U

在这个示例中，我们使用 UNPIVOT 将 Sales 表中的列数据反透视为行数据。反透视后的结果集中，每个 Product1、Product2、Product3 都是一个新的行，它们的值是 SalesAmount。