关于分组查询的一道常见sql题
背景：一道常见的sql的分组计算题，题目如下：
按照角色分组算出每个角色按有办公室和没办公室的统计人数（列出角色，数量，有无办公室，注意一个角色如果部分有办公室，部分没有需分开统计）
如下，构造测试环境与对应的测试数据来验证
1.打开虚拟机，确认开启mysql服务
![]()
2.打开navicat，连接数据库，testdb库
![]()
3.创建表，主键设定为姓名
create table Employee (
Role VARCHAR(64) DEFAULT NULL,
Name VARCHAR(64) PRIMARY KEY,
Building VARCHAR(64) DEFAULT NULL,
Years_employed VARCHAR(64) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3;
![]()
4.插入测试数据：（手动或inset）
INSERT INTO `testdb`.`Employee` (`Role`, `Name`, `Building`, `Years_employed`) VALUES ('Engineer', 'Becky A.', '1e', '4');
INSERT INTO `testdb`.`Employee` (`Role`, `Name`, `Building`, `Years_employed`) VALUES ('Engineer', 'Dan B.', '1e', '2');
INSERT INTO `testdb`.`Employee` (`Role`, `Name`, `Building`, `Years_employed`) VALUES ('Engineer', 'Sharon F.', '1e', '6');
INSERT INTO `testdb`.`Employee` (`Role`, `Name`, `Building`, `Years_employed`) VALUES ('Engineer', 'Dan M.', '1e', '4');
INSERT INTO `testdb`.`Employee` (`Role`, `Name`, `Building`, `Years_employed`) VALUES ('Engineer', 'Malcom S.', '1e', '1\r\n');
INSERT INTO `testdb`.`Employee` (`Role`, `Name`, `Building`, `Years_employed`) VALUES ('Artist', 'Tylar S.', '2w', '2');
INSERT INTO `testdb`.`Employee` (`Role`, `Name`, `Building`, `Years_employed`) VALUES ('Artist', 'Sherman D.', '2w', '8');
INSERT INTO `testdb`.`Employee` (`Role`, `Name`, `Building`, `Years_employed`) VALUES ('Artist', 'Jakob J.', '2w', '6');
INSERT INTO `testdb`.`Employee` (`Role`, `Name`, `Building`, `Years_employed`) VALUES ('Artist', 'Lillia A.', '2w', '7');
INSERT INTO `testdb`.`Employee` (`Role`, `Name`, `Building`, `Years_employed`) VALUES ('Artist', 'Brandon J.', '2w', '7');
INSERT INTO `testdb`.`Employee` (`Role`, `Name`, `Building`, `Years_employed`) VALUES ('Manager', 'Scott K.', '1e', '9');
INSERT INTO `testdb`.`Employee` (`Role`, `Name`, `Building`, `Years_employed`) VALUES ('Manager', 'Shirlee M.', '1e', '3');
INSERT INTO `testdb`.`Employee` (`Role`, `Name`, `Building`, `Years_employed`) VALUES ('Manager', 'Manager', '2w', '6');
INSERT INTO `testdb`.`Employee` (`Role`, `Name`, `Building`, `Years_employed`) VALUES ('Engineer', 'Yancy I.', 'nu
5.求解sql，分别根据不同的条件使用where过滤，然后group by再count计算，最后用union all进行连接
sql语句如下：
select Role,count(*) as number,"Y"as haveBuilding from Employees where Building <> 'NULL' GROUP BY Role
union all
select Role,count(*) as number,"N" as haveBuilding from Employees where Building = 'NULL' GROUP BY Role
查看运行结果：
![]()
6.另一种解决，使用case when语句是否满足的条件并且group by时添加此条件：
sql语句如下：
select Role,count(name),
case when Building <> 'NUll' Then "Y" else "N" end as haveBuilding
from Employees
GROUP BY Role,haveBuilding
查询运行结果：
![]()
7.扩展，使用GROUP_CONCAT函数可以在分组的同时连接所需查看的固定字段的值。
sql语句如下：
select Role,count(name),
case when Building <> 'NUll' Then "Y" else "N" end as haveBuilding,GROUP_CONCAT(Building)
from Employees
GROUP BY Role,haveBuilding
查看运行结果：
![]()
8,扩展，使用GROUP_CONCAT结合DISTINCT语句可以将连接后内重复的值过滤
sql语句如下：
select Role,count(name),
case when Building <> 'NUll' Then "Y" else "N" end as haveBuilding,GROUP_CONCAT(DISTINCT Building) as BuildingInfo
from Employees
GROUP BY Role,haveBuilding
查看运行结果：
![]()
2.打开navicat，连接数据库，testdb库
3.创建表，主键设定为姓名
create table Employee (
Role VARCHAR(64) DEFAULT NULL,
Name VARCHAR(64) PRIMARY KEY,
Building VARCHAR(64) DEFAULT NULL,
Years_employed VARCHAR(64) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3;
4.插入测试数据：（手动或inset）
INSERT INTO `testdb`.`Employee` (`Role`, `Name`, `Building`, `Years_employed`) VALUES ('Engineer', 'Becky A.', '1e', '4');
INSERT INTO `testdb`.`Employee` (`Role`, `Name`, `Building`, `Years_employed`) VALUES ('Engineer', 'Dan B.', '1e', '2');
INSERT INTO `testdb`.`Employee` (`Role`, `Name`, `Building`, `Years_employed`) VALUES ('Engineer', 'Sharon F.', '1e', '6');
INSERT INTO `testdb`.`Employee` (`Role`, `Name`, `Building`, `Years_employed`) VALUES ('Engineer', 'Dan M.', '1e', '4');
INSERT INTO `testdb`.`Employee` (`Role`, `Name`, `Building`, `Years_employed`) VALUES ('Engineer', 'Malcom S.', '1e', '1\r\n');
INSERT INTO `testdb`.`Employee` (`Role`, `Name`, `Building`, `Years_employed`) VALUES ('Artist', 'Tylar S.', '2w', '2');
INSERT INTO `testdb`.`Employee` (`Role`, `Name`, `Building`, `Years_employed`) VALUES ('Artist', 'Sherman D.', '2w', '8');
INSERT INTO `testdb`.`Employee` (`Role`, `Name`, `Building`, `Years_employed`) VALUES ('Artist', 'Jakob J.', '2w', '6');
INSERT INTO `testdb`.`Employee` (`Role`, `Name`, `Building`, `Years_employed`) VALUES ('Artist', 'Lillia A.', '2w', '7');
INSERT INTO `testdb`.`Employee` (`Role`, `Name`, `Building`, `Years_employed`) VALUES ('Artist', 'Brandon J.', '2w', '7');
INSERT INTO `testdb`.`Employee` (`Role`, `Name`, `Building`, `Years_employed`) VALUES ('Manager', 'Scott K.', '1e', '9');
INSERT INTO `testdb`.`Employee` (`Role`, `Name`, `Building`, `Years_employed`) VALUES ('Manager', 'Shirlee M.', '1e', '3');
INSERT INTO `testdb`.`Employee` (`Role`, `Name`, `Building`, `Years_employed`) VALUES ('Manager', 'Manager', '2w', '6');
INSERT INTO `testdb`.`Employee` (`Role`, `Name`, `Building`, `Years_employed`) VALUES ('Engineer', 'Yancy I.', 'nu
5.求解sql，分别根据不同的条件使用where过滤，然后group by再count计算，最后用union all进行连接
sql语句如下：
select Role,count(*) as number,"Y"as haveBuilding from Employees where Building <> 'NULL' GROUP BY Role
union all
select Role,count(*) as number,"N" as haveBuilding from Employees where Building = 'NULL' GROUP BY Role
查看运行结果：
6.另一种解决，使用case when语句是否满足的条件并且group by时添加此条件：
sql语句如下：
select Role,count(name),
case when Building <> 'NUll' Then "Y" else "N" end as haveBuilding
from Employees
GROUP BY Role,haveBuilding
查询运行结果：
7.扩展，使用GROUP_CONCAT函数可以在分组的同时连接所需查看的固定字段的值。
sql语句如下：
select Role,count(name),
case when Building <> 'NUll' Then "Y" else "N" end as haveBuilding,GROUP_CONCAT(Building)
from Employees
GROUP BY Role,haveBuilding
查看运行结果：
8,扩展，使用GROUP_CONCAT结合DISTINCT语句可以将连接后内重复的值过滤
sql语句如下：
select Role,count(name),
case when Building <> 'NUll' Then "Y" else "N" end as haveBuilding,GROUP_CONCAT(DISTINCT Building) as BuildingInfo
from Employees
GROUP BY Role,haveBuilding
查看运行结果：