mysql group by多字段的应用

前言

一个业余爱好编程的老哥向我求助一个sql问题，用一条sql计算每角色的平均分后得到每姓名的得分，然后按姓名分类汇总，猛一看这个场景还真没遇到过，期间尝试了好多种方案，都得不到想要的结果，最后发现了group by多字段完美解决，太应景了，故在这里做个记录，sql牛逼的可以来试试你的解法。详细示意图如下：

sql元数据脚本

SET FOREIGN_KEY_CHECKS=0;

-- ----------------------------
-- Table structure for test
-- ----------------------------
DROP TABLE IF EXISTS `test`;
CREATE TABLE `test` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(255) CHARACTER SET utf8mb4 DEFAULT NULL,
  `role` varchar(255) CHARACTER SET utf8mb4 DEFAULT NULL,
  `score` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=8 DEFAULT CHARSET=utf8;

-- ----------------------------
-- Records of test
-- ----------------------------
INSERT INTO `test` VALUES ('1', '张三', '员工', '8');
INSERT INTO `test` VALUES ('2', '张三', '中层', '9');
INSERT INTO `test` VALUES ('3', '张三', '员工', '6');
INSERT INTO `test` VALUES ('4', '李四', '员工', '7');
INSERT INTO `test` VALUES ('5', '李四', '中层', '8');
INSERT INTO `test` VALUES ('6', '李四', '中层', '9');
INSERT INTO `test` VALUES ('7', '张三', '中层', '7');

第一步：

通过对name、role字段分组，得到某个人、某个角色的平均分，如

SELECT name, SUM(score) / COUNT(*) AS socre1 FROM test GROUP BY NAME,role

第二步：

拿到第一步的结果集后，针对姓名分组，使用sum求平均分的合计值，sql如下：

SELECT
	temp. NAME '姓名',
	SUM(temp.socre1) AS '最后得分'
FROM
	(
		SELECT name, SUM(score) / COUNT(*) AS socre1 FROM test GROUP BY NAME,role
	) temp
GROUP BY NAME

结语

在解这个sql的过程中，走了好多弯路，尝试了诸如按name\role列分组在合并查询等等错误的方法。平时sql写的不多，接触到的业务场景限制了我对sql认知，如果没发现group by可以多字段，那么这个题目在博主这里基本就无解了，欢迎sql大拿秀一下更牛逼的解法