用SQL查询创建水平、垂直直方图

mysql> select * from t1;
+--------+
| deptno |
+--------+
|     10 |
|     10 |
|     10 |
|     20 |
|     20 |
|     20 |
|     20 |
|     30 |
|     30 |
|     30 |
|     30 |
|     30 |
|     30 |
+--------+
13 rows in set (0.00 sec)

DEPTNO CNT
------ ----------
    10 ***
    20 *****
    30 ******

mysql> select deptno,lpad('*',count(*),'*') cnt from t1 group by deptno;
+--------+--------+
| deptno | cnt    |
+--------+--------+
|     10 | ***    |
|     20 | ****   |
|     30 | ****** |
+--------+--------+
3 rows in set (0.00 sec)

D10 D20 D30
--- --- ---
        *
    *   *
    *   *
*   *   *
*   *   *
*   *   *

mysql> select max(d10) d10,max(d20) d20,max(d30) d30 from 
    -> (select row_number() over (partition by deptno) rn,
    ->        case when deptno=10 then '*' else '' end d10,
    ->        case when deptno=20 then '*' else '' end d20,
    ->        case when deptno=30 then '*' else '' end d30 
    ->   from t1) t group by rn;
+------+------+------+
| d10  | d20  | d30  |
+------+------+------+
| *    | *    | *    |
| *    | *    | *    |
| *    | *    | *    |
|      | *    | *    |
|      |      | *    |
|      |      | *    |
+------+------+------+
6 rows in set (0.00 sec)