关于分组查询的一道常见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

查看运行结果：