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);
Group by语句是在各条记录聚合之前选择分组条件
1. 按照单个条件分组.
select * from test.courses group by student;
2.按照多个条件分组
select * from test.courses group by student,class;
因为student属性相同的行class却不相同,所以查询出来的结果并不会像单个条件分组那样去重
3.与order by一起使用
例如想要得到一张表,按照每一个学生进行排序,并且在每一位学生后面按照成绩对该学生的各科进行排序
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;
这样才是正确的,先按照学生和科目进行分组,再对整个表按照学生排序,再次在排序好的表上按照成绩进行排序
4.与having一起使用(HAVING见下一篇文章)
select * from test.courses group by student,class having score>70 order by student, score desc;
本博客参考了其他小伙伴的文章另外添加了自己的拙见!
参考博客:https://www.cnblogs.com/hhe0/p/9556070.html,尊重原创!