MySQL 对不同组/分区进行聚集

痛定思痛。 2022-11-01 00:48 165阅读 0赞

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

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

# 一.需求 #

同时按不同维进行聚集。

例如，要返回这样的结果集:列出每个员工的名字、他所在的部门、该部门的员工数(包括他自己)、与他有同样职位的员工数(也包括他自己)以及emp表中的员工总数。

起结果集应该如下所示:  
±-------±-------±-----------±----------±--------±------+  
| ename | deptno | deptno\_cnt | job | job\_cnt | total |  
±-------±-------±-----------±----------±--------±------+  
| SCOTT | 20 | 5 | ANALYST | 2 | 14 |  
| FORD | 20 | 5 | ANALYST | 2 | 14 |  
| MILLER | 10 | 3 | CLERK | 4 | 14 |  
| SMITH | 20 | 5 | CLERK | 4 | 14 |  
| ADAMS | 20 | 5 | CLERK | 4 | 14 |  
| JAMES | 30 | 6 | CLERK | 4 | 14 |  
| CLARK | 10 | 3 | MANAGER | 3 | 14 |  
| JONES | 20 | 5 | MANAGER | 3 | 14 |  
| BLAKE | 30 | 6 | MANAGER | 3 | 14 |  
| KING | 10 | 3 | PRESIDENT | 1 | 14 |  
| ALLEN | 30 | 6 | SALESMAN | 4 | 14 |  
| WARD | 30 | 6 | SALESMAN | 4 | 14 |  
| MARTIN | 30 | 6 | SALESMAN | 4 | 14 |  
| TURNER | 30 | 6 | SALESMAN | 4 | 14 |  
±-------±-------±-----------±----------±--------±------+

# 二.解决方案 #

窗口函数使这个问题相当容易解决。如果不能使用窗口函数，也可以使用标量子查询。

select  ename,
            deptno,
            count(*) over w1 as 'deptno_cnt',
            job,
            count(*) over w2 as 'job_cnt',
            count(*) over w3 as 'total'
      from  emp
    window w1 as (partition by deptno),
            w2 as (partition by job),
            w3 as ()

测试记录:

mysql> select  ename,
        ->         deptno,
        ->         count(*) over w1 as 'deptno_cnt',
        ->         job,
        ->         count(*) over w2 as 'job_cnt',
        ->         count(*) over w3 as 'total'
        ->   from  emp
        -> window w1 as (partition by deptno),
        ->         w2 as (partition by job),
        ->         w3 as ();
    +--------+--------+------------+-----------+---------+-------+
    | ename  | deptno | deptno_cnt | job       | job_cnt | total |
    +--------+--------+------------+-----------+---------+-------+
    | SCOTT  |     20 |          5 | ANALYST   |       2 |    14 |
    | FORD   |     20 |          5 | ANALYST   |       2 |    14 |
    | MILLER |     10 |          3 | CLERK     |       4 |    14 |
    | SMITH  |     20 |          5 | CLERK     |       4 |    14 |
    | ADAMS  |     20 |          5 | CLERK     |       4 |    14 |
    | JAMES  |     30 |          6 | CLERK     |       4 |    14 |
    | CLARK  |     10 |          3 | MANAGER   |       3 |    14 |
    | JONES  |     20 |          5 | MANAGER   |       3 |    14 |
    | BLAKE  |     30 |          6 | MANAGER   |       3 |    14 |
    | KING   |     10 |          3 | PRESIDENT |       1 |    14 |
    | ALLEN  |     30 |          6 | SALESMAN  |       4 |    14 |
    | WARD   |     30 |          6 | SALESMAN  |       4 |    14 |
    | MARTIN |     30 |          6 | SALESMAN  |       4 |    14 |
    | TURNER |     30 |          6 | SALESMAN  |       4 |    14 |
    +--------+--------+------------+-----------+---------+-------+
    14 rows in set (0.00 sec)

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