sql Group by语句

create database test;

CREATE TABLE test.courses 
(
`id` INT(11) UNSIGNED NOT NULL AUTO_INCREMENT COMMENT ‘自增id‘,

`student` VARCHAR(255) DEFAULT NULL COMMENT ‘学生‘,

`class` VARCHAR(255) DEFAULT NULL COMMENT ‘课程‘,

`score` INT(255) DEFAULT NULL COMMENT ‘分数‘,

PRIMARY KEY (`id`),
UNIQUE KEY `course` (`student`, `class`)
) 
ENGINE=InnoDB DEFAULT CHARSET=utf8;


INSERT INTO test.courses(`student`, `class`, `score`) VALUES(‘A‘, ‘Math‘, 90);

INSERT INTO test.courses(`student`, `class`, `score`) VALUES(‘A‘, ‘Chinese‘, 80);

INSERT INTO test.courses(`student`, `class`, `score`) VALUES(‘A‘, ‘English‘, 70);

INSERT INTO test.courses(`student`, `class`, `score`) VALUES(‘A‘, ‘History‘, 80);


INSERT INTO test.courses(`student`, `class`, `score`) VALUES(‘B‘, ‘Math‘, 73);

INSERT INTO test.courses(`student`, `class`, `score`) VALUES(‘B‘, ‘Chinese‘, 60);

INSERT INTO test.courses(`student`, `class`, `score`) VALUES(‘B‘, ‘English‘, 70);

INSERT INTO test.courses(`student`, `class`, `score`) VALUES(‘B‘, ‘History‘, 90);


INSERT INTO test.courses(`student`, `class`, `score`) VALUES(‘C‘, ‘Math‘, 70);

INSERT INTO test.courses(`student`, `class`, `score`) VALUES(‘C‘, ‘Chinese‘, 50);

INSERT INTO test.courses(`student`, `class`, `score`) VALUES(‘C‘, ‘English‘, 20);

INSERT INTO test.courses(`student`, `class`, `score`) VALUES(‘C‘, ‘History‘, 10);


INSERT INTO test.courses(`student`, `class`, `score`) VALUES(‘D‘, ‘Math‘, 53);

INSERT INTO test.courses(`student`, `class`, `score`) VALUES(‘D‘, ‘Chinese‘, 32);

INSERT INTO test.courses(`student`, `class`, `score`) VALUES(‘D‘, ‘English‘, 99);

INSERT INTO test.courses(`student`, `class`, `score`) VALUES(‘D‘, ‘History‘, 100);

select * from test.courses group by student;

select * from test.courses group by student,class;

select * from test.courses group by student,class order by score desc ;

这样写是错误的，因为最后的order by是作用于整体的表上的，并不是在按照学生进行排序之后的再次排序

select * from test.courses group by student,class order by student, score desc;
这样才是正确的，先按照学生和科目进行分组，再对整个表按照学生排序，再次在排序好的表上按照成绩进行排序

select * from test.courses group by student,class having score>70 order by student, score desc;