有一个scores表,表结构和数据如下:
id, stu_id, name,course, grade
1,1,贾万, 语文, 90
2,1,贾万 ,数学 ,100
3,2,毛之远 ,语文 ,97
4,2,毛之远,数学 ,100
5,2 ,毛之远,物理(选修),90
通过SQL改写成这样:
学号,姓名,课程,综合平均成绩
1,贾万 | 语文,数学| 95
2,毛之远 |语文,数学,英语 |95.67
先建表和插入数据:
SET FOREIGN_KEY_CHECKS=0;
-- ----------------------------
-- Table structure for scores
-- ----------------------------
DROP TABLE IF EXISTS `scores`;
CREATE TABLE `scores` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`stu_id` int(11) NOT NULL,
`name` varchar(20) DEFAULT NULL,
`course` varchar(20) DEFAULT NULL,
`grade` decimal(10,0) DEFAULT NULL,
PRIMARY KEY (`id`,`stu_id`)
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8mb4;
-- ----------------------------
-- Records of scores
-- ----------------------------
INSERT INTO `scores` VALUES ('1', '1', '贾万', '语文', '90');
INSERT INTO `scores` VALUES ('2', '1', '贾万', '数学', '100');
INSERT INTO `scores` VALUES ('3', '2', '毛之远', '语文', '97');
INSERT INTO `scores` VALUES ('4', '2', '毛之远', '数学', '100');
INSERT INTO `scores` VALUES ('5', '2', '毛之远', '物理(选修)', '90');
分析:先把平均成绩求出来
select stu_id,name, avg(grade) as "综合成绩" from scores group by name;
然后插入想要的列:
select stu_id,name,"课程", avg(grade) as "综合成绩" from scores group by name;
然后思考怎么把 "课程" 这一个条件的结果改变成语文,数据,物理的整合结果。
我们根据课程去查询时,发现会返回多行课程结果,所以问题可以转变为多行合并为一行。
百度“mysql 多行合并”,发现了这个链接:http://www.cnblogs.com/guaiguaipaizz/p/6501676.html
其中提到了 GROUP_CONCAT 的用法 和 SEPARATOR 的用法,源码样例原封不动的拷贝过来(如果侵权了,我随时可以撤回,不过大家都是用于学习,不是商业用途,还请网开一面。)
SELECT am.activeId,GROUP_CONCAT(m.modelName SEPARATOR ',') modelName FROM activemodel am JOIN model m ON am.modelId=m.modelId WHERE m.valid=1 GROUP BY am.activeId
然后改写我们的案例SQL:
select stu_id,name,GROUP_CONCAT(course SEPARATOR ',') as "课程", avg(grade) as "综合成绩" from scores group by name;