SQL:用SQL统计学生成绩 原来有这么多玩法

• 准备数据

1. 建表

CREATE TABLE `stuscore` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `name` varchar(50) DEFAULT NULL,
  `subject` varchar(50) DEFAULT NULL,
  `score` bigint(20) DEFAULT NULL,
  `stuid` bigint(20) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=37 DEFAULT CHARSET=utf8

2. 测试数据

insert into test.stuscore(name,subject,score,stuid) values ('张三','数学',89,1);
insert into test.stuscore(name,subject,score,stuid) values ('张三','语文',80,1);
insert into test.stuscore(name,subject,score,stuid) values ('张三','英语',70,1);
insert into test.stuscore(name,subject,score,stuid) values ('李四','数学',90,2);
insert into test.stuscore(name,subject,score,stuid) values ('李四','语文',70,2);
insert into test.stuscore(name,subject,score,stuid) values (&