比如说显示每个部门的职工数吧
横向直方图
SQL> select deptno,
2 lpad('*',count(*),'*') as cnt
3 from emp
4 group by deptno;
DEPTNO CNT
---------- ------------------------------
30 ******
20 *****
10 ***
纵向直方图
SQL> select
2 max(deptno_10) d10,
3 max(deptno_20) d20,
4 max(deptno_30) d30
5 from
6 (
7 select row_number() over(partition by deptno order by empno) rn,
8 case when deptno=10 then '*' else null end deptno_10,
9 case when deptno=20 then '*' else null end deptno_20,
10 case when deptno=30 then '*' else null end deptno_30
11 from emp
12 )
13 group by rn
14 order by 1 desc,2 desc,3 desc;
D D D
- - -
*
* *
* *
* * *
* * *
* * *
6 rows selected.
SQL> col d10 format a10 --格式化一下
SQL> col d20 format a10
SQL> col d30 format a10
SQL> /
D10 D20 D30
---------- ---------- ----------
*
* *
* *
* * *
* * *
* * *
6 rows selected.
这个查询长了点,分开几步试试,看看里面子查询的结果
SQL> select row_number() over(partition by deptno order by empno) rn,
2 case when deptno=10 then '*' else null end deptno_10,
3 case when deptno=20 then '*' else null end deptno_20,
4 case when deptno=30 then '*' else null end deptno_30
5 from emp;
RN D D D
---------- - - -
1 *
2 *
3 *
1 *
2 *
3 *
4 *
5 *
1 *
2 *
3 *
RN D D D
---------- - - -
4 *
5 *
6 *
14 rows selected.
现在还剩下一步,就是针对每个CASE表达式使用聚集函数MAX,按RN分组,以便去除结果集中的NULL值。
order by子句中加desc排序是由于在oracle中NULL默认是最大的,按照不同需求以及不用DBMS中对NULL的处理自由搭配就可以了。
SQL> select
2 max(deptno_10) d10,
3 max(deptno_20) d20,
4 max(deptno_30) d30
5 from
6 (
7 select row_number() over(partition by deptno order by empno) rn,
8 case when deptno=10 then '*' else null end deptno_10,
9 case when deptno=20 then '*' else null end deptno_20,
10 case when deptno=30 then '*' else null end deptno_30
11 from emp
12 )
13 group by rn
14 order by 1 ,2 ,3 ;
D10 D20 D30
---------- ---------- ----------
* * *
* * *
* * *
* *
* *
*
6 rows selected.