SQL Server 2005 Beta 2 Transact-SQL 增强功能 1

核心提示： RANK, DENSE_RANKRANK 和 DENSE_RANK 函数非常类似于 ROW_NUMBER 函数，因为它们也按照指定的排序提供排序值，SQL Server 2005 Beta 2 Transact-SQL 增强功能 1(3)，而且可以根据需要在行组（分段）内部提供，但是，第一个梯级表示具有最高得分的演讲

RANK, DENSE_RANK

RANK 和 DENSE_RANK 函数非常类似于 ROW_NUMBER 函数，因为它们也按照指定的排序提供排序值，而且可以根据需要在行组（分段）内部提供。但是，与 ROW_NUMBER 不同的是，RANK 和 DENSE_RANK 向在排序列中具有相同值的行分配相同的排序。当 ORDER BY 列表不唯一，并且您不希望为在 ORDER BY 列表中具有相同值的行分配不同的排序时，RANK 和 DENSE_RANK 很有用。RANK 和 DENSE_RANK 的用途以及两者之间的差异可以用示例进行最好的解释。以下查询按照 score DESC 顺序计算不同演讲者的行号、排序和紧密排序值：

SELECT speaker, track, score,ROW_NUMBER() OVER(ORDER BY score DESC) AS rownum,RANK() OVER(ORDER BY score DESC) AS rnk,DENSE_RANK() OVER(ORDER BY score DESC) AS drnkFROM SpeakerStatsORDER BY score DESC

以下为结果集：

speaker    track      score       rownum rnk drnk---------- ---------- ----------- ------ --- ----Jessica    Dev        9           1      1   1Ron        Dev        9           2      1   1Suzanne    DB         9           3      1   1Kathy      Sys        8           4      4   2Michele    Sys        8           5      4   2Mike       DB         8           6      4   2Kevin      DB         7           7      7   3Brian      Sys        7           8      7   3Joe        Dev        6           9      9   4Robert     Dev        6           10     9   4Dan        Sys        3           11     11  5

正如前面讨论的那样，score 列不唯一，因此不同的演讲者可能具有相同的得分。行号确实代表下降的 score 顺序，但是具有相同得分的演讲者仍然获得不同的行号。但是请注意，在结果中，所有具有相同得分的演讲者都获得相同的排序和紧密排序值。换句话说，当 ORDER BY 列表不唯一时，ROW_NUMBER 是不确定的，而 RANK 和 DENSE_RANK 总是确定的。排序值和紧密排序值之间的差异在于，排序代表：具有较高得分的行号加 1，而紧密排序代表：具有明显较高得分的行号加 1。从您迄今为止已经了解的内容中，您可以推导出当 ORDER BY 列表唯一时，ROW_NUMBER、RANK 和 DENSE_RANK 产生完全相同的值。

NTILE

NTILE 使您可以按照指定的顺序，将查询的结果行分散到指定数量的组 (tile) 中。每个行组都获得不同的号码：第一组为 1，第二组为 2，等等。您可以在函数名称后面的括号中指定所请求的组号，在 OVER 选项的 ORDER BY 子句中指定所请求的排序。组中的行数被计算为 total_num_rows / num_groups。如果有余数 n，则前面 n 个组获得一个附加行。因此，可能不会所有组都获得相等数量的行，但是组大小最大只可能相差一行。例如，以下查询按照 score 降序将三个组号分配给不同的 speaker 行：

SELECT speaker, track, score,ROW_NUMBER() OVER(ORDER BY score DESC) AS rownum,NTILE(3) OVER(ORDER BY score DESC) AS tileFROM SpeakerStatsORDER BY score DESC

以下为结果集：

speaker    track      score       rownum tile---------- ---------- ----------- ------ ----Jessica    Dev        9           1      1Ron        Dev        9           2      1Suzanne    DB         9           3      1Kathy      Sys        8           4      1Michele    Sys        8           5      2Mike       DB         8           6      2Kevin      DB         7           7      2Brian      Sys        7           8      2Joe        Dev        6           9      3Robert     Dev        6           10     3Dan        Sys        3           11     3

在 SpeakerStats 表中有 11 位演讲者。将 11 除以 3 得到组大小 3 和余数 2，这意味着前面 2 个组将获得一个附加行（每个组中有 4 行），而第三个组则不会得到附加行（该组中有 3 行）。组号（tile 号）1 被分配给行 1 到 4，组号 2 被分配给行 5 到 8，组号 3 被分配给行 9 到 11。通过该信息可以生成直方图，并且将项目均匀分布到每个梯级。在我们的示例中，第一个梯级表示具有最高得分的演讲者，第二个梯级表示具有中等得分的演讲者，第三个梯级表示具有最低得分的演讲者。可以使用 CASE 表达式为组号提供说明性的有意义的备选含义：

SELECT speaker, track, score,CASE NTILE(3) OVER(ORDER BY score DESC)WHEN 1 THEN 'High'WHEN 2 THEN 'Medium'WHEN 3 THEN 'Low'END AS scorecategoryFROM SpeakerStatsORDER BY track, speaker

以下为结果集：

speaker    track      score       scorecategory---------- ---------- ----------- -------------Kevin      DB         7           MediumMike       DB         8           MediumSuzanne    DB         9           HighJessica    Dev        9           HighJoe        Dev        6           LowRobert     Dev        6           LowRon        Dev        9           HighBrian      Sys        7           MediumDan        Sys        3           LowKathy      Sys        8           HighMichele    Sys        8           Medium