SQL 变换已排名的结果集|极客教程

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 表。此后，只要多次变换该查询结果直到得出我们希望看到的结果集即可。想想我们创建出了这么复杂的报表，却只读取过一次原始数据，真是非常酷。