在 第一部分 中，我们讨论了 APPLY 和 CTE 这两个T-SQL Enhancement。APPLY实现了Table和TVF的Join，CTE通过创建“临时的View”的方式使问题化繁为简。现在我们接着来讨论另外两个重要的T-SQL Enhancement Items： PIVOT 和 Ranking 。
三、 PIVOT Operator
PIVOT 的中文意思是“在枢轴上转动”， 比如对于一个 2 维坐标，将横坐标变成纵坐标，将纵坐标变成横坐标。反映在一个 Relational Table 上的意思就是：变成为列，变列为行。相信大家在进行报表设计的时候都遇到过类似于这样的需求：统计 2002 年内某个销售人员第一季度每个月处理的订单数。在 AdventureWorks Sample Databse 中， Sales Order 存储于 SaleOrderHeader 这张表中，它的结果如下：
我们一般情况下通过下面的 SQL 实现我们提出的统计功能：
SELECT SalesPersonID,
SUM ( CASE DATEPART (MM,OrderDate) WHEN 1 THEN 1 ELSE 0 END ) AS JAN,
SUM ( CASE DATEPART (MM,OrderDate) WHEN 2 THEN 1 ELSE 0 END ) AS FEB,
SUM ( CASE DATEPART (MM,OrderDate) WHEN 3 THEN 1 ELSE 0 END ) AS MAR,
SUM ( CASE DATEPART (MM,OrderDate) WHEN 4 THEN 1 ELSE 0 END ) AS APR
FROM Sales.SalesOrderHeader
WHERE DATEPART (yyyy,OrderDate) = 2002
GROUP BY SalesPersonID
于是我们得到了这样的统计数据：
通过数据在原表的结构和我们最终获得的结果进行比较，我们发现就像是“旋转”了 90 度，原来的 OrderDate 是存储在每行的基于 Order 的一个属性（行），现在我们要把 Order Date 按照不同月份统计，这样行变成了列。
像这样的需求，我们都可以可以通过 PIVOT 这个操作符来实现，下面就是基于 PIVOT 的 SQL ：
SELECT SalesPersonID, [ 1 ] AS JAN, [ 2 ] AS FEB, [ 3 ] AS MAR, [ 4 ] AS APR
FROM
(
SELECT SalesPersonID, DATEPART (MM,OrderDate) AS MON
FROM Sales.SalesOrderHeader
WHERE DATEPART (yyyy,OrderDate) = 2002
) S
PIVOT
(
COUNT (MON) FOR MON IN ( [ 1 ] , [ 2 ] , [ 3 ] , [ 4 ] )
)
AS P
在上面的例子中，同过下面的SELECT 语句筛选出来的是为经过 PIVOT 的数据。
SELECT SalesPersonID, DATEPART (MM,OrderDate) AS MON
FROM Sales.SalesOrderHeader
WHERE DATEPART (yyyy,OrderDate) = 2002
）就成了我们最终输出的结构了。
PIVOT
(
COUNT (MON) FOR MON IN ( [ 1 ] , [ 2 ] , [ 3 ] , [ 4 ] )
)
如果你第一次见到PIVOT ，可以不能一下明白它的实现，但是只要你是使用了一两次，相信就会很容易地掌握它。与 PIVOT 对应的还以一个操作符 UNPIVOT ，它完成 PIVOT 的逆操作，在这里就不介绍了，如果有兴趣的话，可以参考 SQL Server Books Online 。
四、 Ranking
排序与排名是我们最为常用的统计方式，比如对班级的学生根据成员进行排名，或者按照成绩高低把学生划分成若干梯队：比如最好成绩的 10 名学生属于第一梯队，后 10 名又划分为第二梯队，以此类推。 Ranking 设计的 Key Words 包括： ROW_NUMBER(),RANK(),DENSE_RANK(),NTILE() 。 我们现在就来介绍一下他们的用法和相互之间的差异。
1． 1. ROW_NUMBER()
看到 ROW_NUMBER(), 我想绝大多数人会像想到 Oracle 的 ROWNUM 。他们的作用相似，都是表示某条记录所处的 Index 。 ROW_NUMBER() 比 Oracle 的 ROWNUM 更加强大的是，它可以通过 OVER 语句指定一个进行排序的 Column ，比如： ROW_NUMBER() OVER (ORDER BY CustomerID) 。
我们来看一个例子：对 Sales.SalesOrderHeader 按照 CustomerID 进行排序，并显示每条记录的 Row Number 。
SELECT SalesOrderID,CustomerID,ROW_NUMBER() OVER ( ORDER BY CustomerID) AS RowNum
FROM Sales.SalesOrderHeader
下面是查询结果：
我们发现最终的结果按照 CutomerID 进行排序， RowNum 从 1 开始以此递增，每条记录（不管是否具有相同的 CustomerID ）拥有不同的 RowNum 。
提到排序，我们就不得不提到 Order BY ，如果我们在后面加上 ORDER BY ，并指定不同的排序字段，会出现怎样的结果呢？
SELECT SalesOrderID,CustomerID,ROW_NUMBER() OVER ( ORDER BY CustomerID) AS RowNum
FROM Sales.SalesOrderHeader
ORDER BY SalesOrderID
查询获得的结果是：
从上图中可以看到，最终的结果以 ORDER BY 中指定的 SalesOrderID 进行排序，但是 ROW_NUMBER() 体现的值却是基于 CustmerID 排序的。
由于 ROW_NUMBER () 体现是基于某个确定的字段进行排序后某个 DataRow 所处的位置，所以它不能直接使用到 Aggregate 的 Column 中。比如下面的 SQL 是不合法的：
SELECT CustomerID, COUNT ( * ) AS OrderCount,ROW_NUMBER() OVER ( ORDER BY OrderCount)
FROM Sales.SalesOrderHeader
GROUP BY CustomerID
要是想按照 OrderCount ，可以使用第一部分介绍的 CTE ：
WITH CTE_Order(CustomerID,OrderCount)
AS
(
SELECT CustomerID, COUNT ( * ) AS OrderCount
FROM Sales.SalesOrderHeader
GROUP BY CustomerID
)
SELECT CustomerID,OrderCount,ROW_NUMBER() OVER ( ORDER BY OrderCount)
FROM CTE_Order
2． RANK()
RANK() 的使用和 ROW_NUMBER() 类似。不过它与 ROW_NUMBER() 所不同的是：对于被指定为排序的字段，具有相同值得 Row 对应的返回值相同。比如：
SELECT SalesOrderID,CustomerID,RANK() OVER ( ORDER BY CustomerID) AS RowNum
FROM Sales.SalesOrderHeader
下面是相应的查询结果：
对于 RANK() ，还有一点需要说明的是，它的回返值不是连续的 , 比如第五条记录的 Row_Num 是 5 而不是 2 。如果想实现这样需求，就需要用下面一个 Function ： DENSE_RANK() 。
3． 3. DENSE_RANK()
DENSE_RANK() 实现了一个连续的 Ranking 。比如下面的 SQL ：
SELECT SalesOrderID,CustomerID,DENSE_RANK() OVER ( ORDER BY CustomerID) AS RowNum
FROM Sales.SalesOrderHeader
就来产生如下的查询结果：
4 . NTILE()
上面我们说到划分梯队的问题，这样的问题可以通过 NTILE() Function 来实现。比如我们现在按照 CustomerID 排序，把 CustomerID 为 1 和 2 的划分到 3 梯队中：
SELECT SalesOrderID,CustomerID,NTILE( 3 ) OVER ( ORDER BY CustomerID) AS RowNum
FROM Sales.SalesOrderHeader
WHERE CustomerID < 3
其查询结果为：
我们可以看到，一共 12 条记录，划分为 3 组，平均下来每组 4 条记录。
5． 5. PARTITION BY
上面提到的所有 Ranking 都是基于真个结果基的。而有的时候我们需要将真个结果集按照某个 Column 进行分组，进行基于组的 Ranking 。这就需要 PARTITION BY 了。 PARTITION BY 置于 OVER Clause 中，和 ORDER BY 平级。
比如下面的 SQL 将 Order 记录按照 CustomerID 进行分组，在每组中输出排名（安 OrderDate 排序）：
SELECT SalesOrderID,CustomerID,RANK() OVER (PARTITION BY CustomerID ORDER BY OrderDate) AS RowNum
FROM Sales.SalesOrderHeader
注：本文内容来自互联网，旨在为开发者提供分享、交流的平台。如有涉及文章版权等事宜，请你联系站长进行处理。