MySQL 创建纵向直方图

小鱼儿 2022-10-27 02:18 135阅读 0赞

备注:测试数据库版本为MySQL 8.0

如需要scott用户下建表及录入数据语句，可参考:  
[scott建表及录入数据sql脚本][scott_sql]

# 一.需求 #

生成一个从底部向上延伸的直方图。

例如，采用纵向直方图显示每个部门的职员数，一个星号"\*"表示一个员工。

返回结果集应该如:  
±-----±-----±-----+  
| d10 | d20 | d30 |  
±-----±-----±-----+  
| \* | \* | \* |  
| \* | \* | \* |  
| \* | \* | \* |  
| NULL | \* | \* |  
| NULL | \* | \* |  
| NULL | 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;

测试记录:

mysql> 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  |
    +------+------+------+
    | *    | *    | *    |
    | *    | *    | *    |
    | *    | *    | *    |
    | NULL | *    | *    |
    | NULL | *    | *    |
    | NULL | NULL | *    |
    +------+------+------+
    6 rows in set (0.00 sec)

[scott_sql]: https://www.jianshu.com/p/532fe68924cb