前言
一个业余爱好编程的老哥向我求助一个sql问题,用一条sql计算每角色的平均分后得到每姓名的得分,然后按姓名分类汇总,猛一看这个场景还真没遇到过,期间尝试了好多种方案,都得不到想要的结果,最后发现了group by多字段完美解决,太应景了,故在这里做个记录,sql牛逼的可以来试试你的解法。详细示意图如下:
![f84792b4264cfd5363dd45f530b0a014.png](https://i-blog.csdnimg.cn/blog_migrate/7f9636f495aeccfe32884fec36caf9de.jpeg)
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
![affcd2ecce46800595f0d6c3aded3033.png](https://i-blog.csdnimg.cn/blog_migrate/1865ae0e4db4be7aa0a1908335a95728.jpeg)
第二步:
拿到第一步的结果集后,针对姓名分组,使用sum求平均分的合计值,sql如下:
SELECTtemp. NAME '姓名',SUM(temp.socre1) AS '最后得分'FROM(SELECT name, SUM(score) / COUNT(*) AS socre1 FROM test GROUP BY NAME,role) tempGROUP BY NAME
![f24fe42b15be848c06bb6685188aff9a.png](https://i-blog.csdnimg.cn/blog_migrate/fcbc9b935a90fe14bd3759c799e9e4b4.jpeg)
结语
在解这个sql的过程中,走了好多弯路,尝试了诸如按nameole列分组在合并查询等等错误的方法。平时sql写的不多,接触到的业务场景限制了我对sql认知,如果没发现group by可以多字段,那么这个题目在博主这里基本就无解了,欢迎sql大拿秀一下更牛逼的解法