case when then 结合 group by的使用

1.准备数据

ddl：

CREATE TABLE `t_staff` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
  `department` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
  `education` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=8 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

insert：

INSERT INTO `study`.`t_staff` (`id`, `name`, `department`, `education`) VALUES ('1', '张三', '技术部', '本科');
INSERT INTO `study`.`t_staff` (`id`, `name`, `department`, `education`) VALUES ('2', '李四', '技术部', '本科');
INSERT INTO `study`.`t_staff` (`id`, `name`, `department`, `education`) VALUES ('3', '张一', '销售部', '大专');
INSERT INTO `study`.`t_staff` (`id`, `name`, `department`, `education`) VALUES ('4', '张二', '技术部', '初中');
INSERT INTO `study`.`t_staff` (`id`, `name`, `department`, `education`) VALUES ('5', '李一', '设备部', '小学');
INSERT INTO `study`.`t_staff` (`id`, `name`, `department`, `education`) VALUES ('6', '李二', '设备部', '硕士');
INSERT INTO `study`.`t_staff` (`id`, `name`, `department`, `education`) VALUES ('7', '李三', '销售部', '博士');

2.sql实现

SELECT 
department '部门',
sum(case when education='小学'then 1 ELSE 0 END) '小学', 
sum(case when education='初中'then 1 ELSE 0 END) '初中', 
sum(case when education='大学'then 1 ELSE 0 END) '大学', 
sum(case when education='本科'then 1 ELSE 0 END) '本科', 
sum(case when education='硕士'then 1 ELSE 0 END) '硕士', 
sum(case when education='博士'then 1 ELSE 0 END) '博士'
from t_staff 
GROUP BY department