自学SQL网 难题笔记
自学SQL网 第十一课 Part2
【难题】按角色分组算出每个角色按有办公室和没办公室的统计人数(列出角色,数量,有无办公室,注意一个角色如果部分有办公室,部分没有需分开统计)
原始数据表
| Role | Name | Building | Years_employed |
|---|---|---|---|
| Engineer | Becky A. | 1e | 4 |
| Engineer | Dan B. | 1e | 2 |
| Engineer | Sharon F. | 1e | 6 |
| Engineer | Dan M. | 1e | 4 |
| Engineer | Malcom S. | 1e | 1 |
| Artist | Tylar S. | 2w | 2 |
| Artist | Sherman D. | 2w | 8 |
| Artist | Jakob J. | 2w | 6 |
| Artist | Lillia A. | 2w | 7 |
| Artist | Brandon J. | 2w | 7 |
| Manager | Scott K. | 1e | 9 |
| Manager | Shirlee M. | 1e | 3 |
| Manager | Daria O. | 2w | 6 |
| Engineer | Yancy I. | null | 0 |
| Artist | Oliver P. | null | 0 |
希望输出结果:
| count | Role | bn |
|---|---|---|
| 1 | Artist | 0 |
| 5 | Artist | 1 |
| 1 | Engineer | 0 |
| 5 | Engineer | 1 |
| 3 | Manager | 1 |
题解:
需要子查询语句
先通过子查询语句获得如下表:
select Role,(case when Building is not null then 1 else 0 end) bn
from employees
| Role | Bn |
|---|---|
| Engineer | 1 |
| Engineer | 1 |
| Engineer | 1 |
| Engineer | 1 |
| Engineer | 1 |
| Artist | 1 |
| Artist | 1 |
| Artist | 1 |
| Artist | 1 |
| Artist | 1 |
| Manager | 1 |
| Manager | 1 |
| Manager | 1 |
| Engineer | 0 |
| Artist | 0 |
紧接着再分组统计,即可得到预期结果
select role,count(*),bn
from
(select Role,(case when Building is not null then 1 else 0 end) bn
from employees)
where 1
group by role,bn
--------------------------------------分界线---------------------------------------------------
罪过罪过
想复杂了
直接这样更简洁
select role,count(*),(case when Building is not null then 1 else 0 end) bn
from
employees
where 1
group by role,bn
博客记录自学SQL网第十一课Part2的难题,要求按角色分组,统计每个角色有办公室和没办公室的人数,一个角色部分有、部分没有办公室需分开统计。题解先提到用子查询语句,后发现直接操作更简洁。
1万+

被折叠的 条评论
为什么被折叠?



