GROUP BY的使用

前言

本文介绍 group by 关键字在mysql中的使用

创建数据库

创建如下初始化数据库，供后面测试使用；

DROP  TABLE IF EXISTS `user_info`;
CREATE TABLE `user_info` (
    `id` INT(11) NOT NULL AUTO_INCREMENT COMMENT '主键id',
    `user_id` VARCHAR(50) NOT NULL DEFAULT '' COMMENT '用户编号',
    `grade` VARCHAR(50) NOT NULL DEFAULT '' COMMENT '年级',
        `score` INT(11) DEFAULT 0 COMMENT '分数',
    `class` VARCHAR(50) NOT NULL DEFAULT '' COMMENT '班级',
    PRIMARY KEY (`id`),
    UNIQUE INDEX `uniq_user_id` (`user_id`)
);
INSERT INTO `user_info` (`id`, `user_id`, `grade`,`score`, `class`) VALUES (10, '10230', 'C',11, 'B');
INSERT INTO `user_info` (`id`, `user_id`, `grade`,`score`, `class`) VALUES (9, '10229', 'C',11, 'a');
INSERT INTO `user_info` (`id`, `user_id`, `grade`,`score`, `class`) VALUES (8, '10228', 'B',11, 'b');
INSERT INTO `user_info` (`id`, `user_id`, `grade`,`score`, `class`) VALUES (7, '10227', 'B',11, 'b');
INSERT INTO `user_info` (`id`, `user_id`, `grade`,`score`, `class`) VALUES (6, '10226', 'B',11, 'a');
INSERT INTO `user_info` (`id`, `user_id`, `grade`,`score`, `class`) VALUES (5, '10225', 'B',11, 'a');
INSERT INTO `user_info` (`id`, `user_id`, `grade`,`score`, `class`) VALUES (4, '10224', 'A',11, 'b');
INSERT INTO `user_info` (`id`, `user_id`, `grade`,`score`, `class`) VALUES (3, '10223', 'A',11, 'b');
INSERT INTO `user_info` (`id`, `user_id`, `grade`,`score`, `class`) VALUES (2, '10222', 'A',11, 'a');
INSERT INTO `user_info` (`id`, `user_id`, `grade`,`score`, `class`) VALUES (1, '10221', 'A',11, 'a');

使用样例

• 查询记录中各个grade中的最大值

select max(user_id),grade from user_info group by grade ;

image.png

• 添加过滤条件

select max(user_id),grade from user_info group by grade  having grade>'A'

image.png

• 查询各个grade的总分数

select SUM(score),grade from user_info group by grade ;

image.png