备注:测试数据库版本为MySQL 8.0
如需要scott用户下建表及录入数据语句,可参考:
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)