极客教程SQL 变换已排名的结果集，你想为一个表里的记录排名，然后把它变换成一个 3 列的结果集。这 3 列分别是前 3 名，接下来的 3 个名次，然后是其余各行记录。例如，你希望根据 SAL 值为 EMP 表的员工排名，并把结果变换成一个 3 列的结果集。
SQL 变换已排名的结果集 问题描述
你想为一个表里的记录排名，然后把它变换成一个 3 列的结果集。这 3 列分别是前 3 名，接下来的 3 个名次，然后是其余各行记录。例如，你希望根据 SAL 值为 EMP 表的员工排名，并把结果变换成一个 3 列的结果集。你期望的结果集如下所示。
TOP_3 NEXT_3 REST
--------------- --------------- --------------
KING (5000) BLAKE (2850) TURNER (1500)
FORD (3000) CLARK (2450) MILLER (1300)
SCOTT (3000) ALLEN (1600) MARTIN (1250)
JONES (2975) WARD (1250)
ADAMS (1100)
JAMES (950)
SMITH (800)
SQL 变换已排名的结果集 解决方案
本解决方案的关键是先借助窗口函数 DENSE_RANK OVER 根据 SAL 为员工排名，因为该函数允许 Tie 的存在。有了 DENSE_RANK OVER 函数，我们很容易查看前 3 名的工资，接下来的 3 个次高的工资，以及其余工资。
然后，使用窗口函数 ROW_NUMBER OVER 在每个分组内部（前 3 名、接下来的 3 个名次，或其余各行记录）为员工排序。然后，只要做一次常规的行列变换操作，并借助数据库内置的字符串函数对查询结果做出适当格式化即可。下面的解决方案使用 Oracle 语法。DB2 和 SQL Server 2005 都支持窗口函数，相信你能够适当地修改本解决方案以适用于其他数据库。
1 select max(case grp when 1 then rpad(ename,6) ||
2 ' ('|| sal ||')' end) top_3,
3 max(case grp when 2 then rpad(ename,6) ||
4 ' ('|| sal ||')' end) next_3,
5 max(case grp when 3 then rpad(ename,6) ||
6 ' ('|| sal ||')' end) rest
7 from (
8 select ename,
9 sal,
10 rnk,
11 case when rnk <= 3 then 1
12 when rnk <= 6 then 2
13 else 3
14 end grp,
15 row_number()over (
16 partition by case when rnk <= 3 then 1
17 when rnk <= 6 then 2
18 else 3
19 end
20 order by sal desc, ename
21 ) grp_rnk
22 from (
23 select ename,
24 sal,
25 dense_rank()over(order by sal desc) rnk
26 from emp
27 ) x
28 ) y
29 group by grp_rnk
SQL 变换已排名的结果集 扩展知识
本实例充分展示了窗口函数的威力。上述解决方案看起来很复杂，但是如果我们把它拆解开来一一细看的话，就会发现其实并不难理解。我们先从内嵌视图 X 开始。
select ename,
sal,
dense_rank()over(order by sal desc) rnk
from emp
ENAME SAL RNK
---------- ----- ----------
KING 5000 1
SCOTT 3000 2
FORD 3000 2
JONES 2975 3
BLAKE 2850 4
CLARK 2450 5
ALLEN 1600 6
TURNER 1500 7
MILLER 1300 8
WARD 1250 9
MARTIN 1250 9
ADAMS 1100 10
JAMES 950 11
SMITH 800 12
如上所示，内嵌视图 X 根据 SAL 为员工排序，同时又允许 Tie 的存在。（因为本解决方案使用的是 DENSE_RANK 函数而不是 RANK 函数，DENSE_RANK 不仅允许 Tie 的存在，还能保证名次连续，中间不留空白。）下一步是从内嵌视图 X 里提取出各行数据，并借助 CASE 表达式评估 DENSE_RANK 返回的排名结果从而实现分组。除此之外，还要调用窗口函数 ROW_NUMBER OVER 根据 SAL 对员工进行组内编号（在前面由 CASE 表达式实现的分组里）。所有这些都由内嵌视图 Y 实现的，如下所示。
select ename,
sal,
rnk,
case when rnk <= 3 then 1
when rnk <= 6 then 2
else 3
end grp,
row_number()over (
partition by case when rnk <= 3 then 1
when rnk <= 6 then 2
else 3
end
order by sal desc, ename
) grp_rnk
from (
select ename,
sal,
dense_rank()over(order by sal desc) rnk
from emp
)x
ENAME SAL RNK GRP GRP_RNK
---------- ----- ---- ---- -------
KING 5000 1 1 1
FORD 3000 2 1 2
SCOTT 3000 2 1 3
JONES 2975 3 1 4
BLAKE 2850 4 2 1
CLARK 2450 5 2 2
ALLEN 1600 6 2 3
TURNER 1500 7 3 1
MILLER 1300 8 3 2
MARTIN 1250 9 3 3
WARD 1250 9 3 4
ADAMS 1100 10 3 5
JAMES 950 11 3 6
SMITH 800 12 3 7
查询语句越来越接近最终的形态了，并且如果你是从开始部分（从内嵌视图 X）一直读到这里的话，一定会发现其实该查询并不是那么难以理解。上述查询返回了每个员工的 SAL、RNK（代表该员工的 SAL 在全体员工中的排名）、GRP（根据每个员工的 SAL 产生的分组），以及 GRP_RANK（在每一个分组内基于 SAL 生成的编号）。
现在，我们要执行一次传统的行列翻转，同时使用 Oracle 的字符串连接操作符||把 SAL 附加在 ENAME 的后面。函数 RPAD 确保圆括号里的数值能够上下对齐。最后，针对 GRP_RNK 执行 GROUP BY 操作以确保结果集能包含所有员工。最终的结果集如下所示。
select max(case grp when 1 then rpad(ename,6) ||
' ('|| sal ||')' end) top_3,
max(case grp when 2 then rpad(ename,6) ||
' ('|| sal ||')' end) next_3,
max(case grp when 3 then rpad(ename,6) ||
' ('|| sal ||')' end) rest
from (
select ename,
sal,
rnk,
case when rnk <= 3 then 1
when rnk <= 6 then 2
else 3
end grp,
row_number()over (
partition by case when rnk <= 3 then 1
when rnk <= 6 then 2
else 3
end
order by sal desc, ename
) grp_rnk
from (
select ename,
sal,
dense_rank()over(order by sal desc) rnk
from emp
) x
) y
group by grp_rnk
TOP_3 NEXT_3 REST
--------------- --------------- -------------
KING (5000) BLAKE (2850) TURNER (1500)
FORD (3000) CLARK (2450) MILLER (1300)
SCOTT (3000) ALLEN (1600) MARTIN (1250)
JONES (2975) WARD (1250)
ADAMS (1100)
JAMES (950)
SMITH (800)
仔细观察以上每一步的查询语句，我们会发现直接读取 EMP 表的操作其实只发生了一次。窗口函数最为引人注目的功能之一就是，只需访问一次原始数据就可以完成很多复杂的任务。不需要自连接或临时表，只要准备好必要的基础数据集，剩下的工作交给窗口函数处理就行了。我们只需要在内嵌视图 X 中访问一次 EMP 表。此后，只要多次变换该查询结果直到得出我们希望看到的结果集即可。想想我们创建出了这么复杂的报表，却只读取过一次原始数据，真是非常酷。