极客教程SQL 创建垂直直方图，你想生成一个从下向上增长的直方图。例如，你希望以垂直直方图的方式显示每个部门的员工人数，每个 * 代表一个员工。你希望返回如下所示的结果集。
SQL 创建垂直直方图 问题描述
你想生成一个从下向上增长的直方图。例如，你希望以垂直直方图的方式显示每个部门的员工人数，每个 * 代表一个员工。你希望返回如下所示的结果集。
D10 D20 D30
--- --- ---
*
* *
* *
* * *
* * *
* * *
SQL 创建垂直直方图 解决方案
我们将以 12.2 节中的方法为基础解决本问题。
DB2、Oracle 和 SQL Server
使用窗口函数 ROW_NUMBER OVER 为每个 DEPTNO 的每一个 * 生成唯一的序号。使用聚合函数 MAX 变换结果集，并针对 ROW_NUMBER OVER 函数的返回值执行 GROUP BY。SQL Server 用户不要在该 ORDER BY 子句中使用 DESC。
1 select max(deptno_10) d10,
2 max(deptno_20) d20,
3 max(deptno_30) d30
4 from (
5 select row_number()over(partition by deptno order by empno) rn,
6 case when deptno=10 then '*' else null end deptno_10,
7 case when deptno=20 then '*' else null end deptno_20,
8 case when deptno=30 then '*' else null end deptno_30
9 from emp
10 ) x
11 group by rn
12 order by 1 desc, 2 desc, 3 desc
PostgreSQL 和 MySQL
使用标量子查询为每个 DEPTNO 的每一个 * 生成唯一的序号。针对内嵌视图 X 的返回值调用聚合函数 MAX，同时也针对 RNK 执行 GROUP BY 以实现结果集变换。MySQL 用户不要在 ORDER BY 子句中使用 DESC。
1 select max(deptno_10) as d10,
2 max(deptno_20) as d20,
3 max(deptno_30) as d30
4 from (
5 select case when e.deptno=10 then '*' else null end deptno_10,
6 case when e.deptno=20 then '*' else null end deptno_20,
7 case when e.deptno=30 then '*' else null end deptno_30,
8 (select count(*) from emp d
9 where e.deptno=d.deptno and e.empno < d.empno ) as rnk
10 from emp e
11 ) x
12 group by rnk
13 order by 1 desc, 2 desc, 3 desc
SQL 创建垂直直方图 扩展知识
DB2、Oracle 和 SQL Server
首先使用窗口函数 ROW_NUMBER 为每个 DEPTNO 的每一个 * 生成唯一的序号。使用 CASE 表达式为每个部门的每个员工返回一个 *。
select row_number()over(partition by deptno order by empno) rn,
case when deptno=10 then '*' else null end deptno_10,
case when deptno=20 then '*' else null end deptno_20,
case when deptno=30 then '*' else null end deptno_30
from emp
RN DEPTNO_10 DEPTNO_20 DEPTNO_30
-- ---------- ---------- ---------
1 *
2 *
3 *
1 *
2 *
3 *
4 *
5 *
1 *
2 *
3 *
4 *
5 *
6 *
下一步也是最后一步，针对每个 CASE 表达式调用聚合函数 MAX，并基于 RN 分组剔除掉 Null 值。以 ASC 或 DESC 方式排序取决于数据库如何对 Null 值排序。
select max(deptno_10) d10,
max(deptno_20) d20,
max(deptno_30) d30
from (
select row_number()over(partition by deptno order by empno) rn,
case when deptno=10 then '*' else null end deptno_10,
case when deptno=20 then '*' else null end deptno_20,
case when deptno=30 then '*' else null end deptno_30
from emp
) x
group by rn
order by 1 desc, 2 desc, 3 desc
D10 D20 D30
--- --- ---
*
* *
* *
* * *
* * *
* * *
PostgreSQL 和 MySQL
首先，使用标量子查询为每个 DEPTNO 的每一个 * 生成唯一的序号。该标量子查询基于 EMPNO 为每个 DEPTNO 的员工生成序号，因此不可能有重复项。使用 CASE 表达式为每个部门的每一个员工返回一个 *。
select case when e.deptno=10 then '*' else null end deptno_10,
case when e.deptno=20 then '*' else null end deptno_20,
case when e.deptno=30 then '*' else null end deptno_30,
(select count(*) from emp d
where e.deptno=d.deptno and e.empno < d.empno ) as rnk
from emp e
DEPTNO_10 DEPTNO_20 DEPTNO_30 RNK
---------- ---------- ---------- ----------
* 4
* 5
* 4
* 3
* 3
* 2
* 2
* 2
* 1
* 1
* 1
* 0
* 0
* 0
然后，针对每个 CASE 表达式调用聚合函数 MAX。这样一来，按照 RNK 分组后就能够从结果集中剔除掉 Null 值了。以 ASC 或 DESC 方式排序取决于数据库如何对 Null 值排序。
select max(deptno_10) as d10,
max(deptno_20) as d20,
max(deptno_30) as d30
from (
select case when e.deptno=10 then '*' else null end deptno_10,
case when e.deptno=20 then '*' else null end deptno_20,
case when e.deptno=30 then '*' else null end deptno_30,
(select count(*) from emp d
where e.deptno=d.deptno and e.empno < d.empno ) as rnk
from emp e
) x
group by rnk
order by 1 desc, 2 desc, 3 desc
D10 D20 D30
--- --- ---
*
* *
* *
* * *
* * *
* * *