关于mysql分组的探索

mysql分组的探讨

mysql分组的关键字：group by

解释说明：GROUP BY语法可以根据给定数据列的每个成员对查询结果进行分组统计，最终得到一个分组汇总表。SELECT子句中的列名必须为分组列或列函数。

从解释中就可以说明分组的用途是干什么用的。具体统计怎么用后面再说，先看红色标记的后面一句，select子句中的列明必须为分组列或者函数。

测试表：

CREATE TABLE `user_test` (  `id` int(11) NOT NULL AUTO_INCREMENT,  `username` varchar(32) DEFAULT NULL,  `age` int(11) DEFAULT NULL,  `classaNo` varchar(255) DEFAULT NULL,  PRIMARY KEY (`id`)) ENGINE=MyISAM AUTO_INCREMENT=78 DEFAULT CHARSET=utf8;

测试数据：

1f110012w120013z120024l110025test0003

探索：select子句中的列明必须为分组列或者函数

sql1：

select           ut.classaNofrom             user_test utgroup by             ut.classaNo

结果：

classaNo001002003

按照要求确实是分组了，通过classaNo，并且列是分组的列。但是如何知道该分组下的具体人员是xx呢？

介绍一个函数：

group_concat（）函数：表示的在分组后，将该栏的数据用数组的形式显示在里面。

sql2：

select           ut.classaNo,group_concat(ut.username)from             user_test utgroup by             ut.classaNo

结果：

classaNo     group_concat(ut.username)001             f,w002             z,l003             test

确实是把每个组中的username通过逗号的方式连接起来的。

引出一个问题，列明必须是分组列或者是函数，如果是非分组列会是神马现象？

sql3：

select           ut.classaNo,group_concat(ut.username),username,agefrom             user_test utgroup by             ut.classaNo

结果：

classaNo     group_concat(ut.username)   username     age001             f,w                        f           11002             z,l                        z           12003             test                       test        0

根据结果所示，若是非分组列，该列的值则是第一行显示的数据的列。

探索：结合group by使用的函数

1.count(*)              统计该分组有多少条记录

2.sum(*)                该组某个列求和

3.max（*）           求出该组中某列最大的值

4.min（*）            求出该组中最小的值

5.avg(*)                  求出该组的平均值

ps：group by 分组之后添加条件，后面条件也必须是函数

例如：

sql4:

查询分组后的username至少有2个组

select           ut.classaNo,group_concat(ut.username),count(ut.username)from             user_test utgroup by             ut.classaNohaving            count(ut.username) >= 2

实际项目中案例sql5
求出分组中最大的一条记录值，数据库表结构如下：

-- Table "studycase_student_grade" DDLCREATE TABLE `studycase_student_grade` (  `id` varchar(32) NOT NULL DEFAULT '' COMMENT 'ID',  `schoolId` int(4) NOT NULL COMMENT '校区编码',  `classCode` varchar(100) NOT NULL COMMENT '班级号',  `className` varchar(100) NOT NULL COMMENT '班级名称',  `courseCode` varchar(200) DEFAULT NULL COMMENT '课程编号',  `teacherEmail` varchar(100) NOT NULL COMMENT '教师邮箱',  `teacherName` varchar(100) NOT NULL COMMENT '教师姓名',  `lessonNo` int(4) NOT NULL COMMENT '课次',  `lessonTime` varchar(50) NOT NULL COMMENT '上课时间',  `studentNo` varchar(100) NOT NULL COMMENT '学员号',  `studentName` varchar(100) NOT NULL COMMENT '学生姓名',  `gradeType` int(4) DEFAULT NULL COMMENT '成绩类型',  `fullMarks` int(4) DEFAULT NULL COMMENT '满分',  `realGrade` int(4) NOT NULL COMMENT '真实成绩',  `invalid` smallint(2) NOT NULL COMMENT '是否有效1-有效，0-无效',  `createTime` datetime NOT NULL,  `updateTime` datetime DEFAULT NULL,  PRIMARY KEY (`id`)) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='学习情况-学生成绩表';

要求：按班级分组，创建时间进行排序，一般group by分组得道的记录都是第一条的，如何取出最大的时间所在的记录，在进行排序了？
思路：
a.求分组后createTime max最大的记录

(select  temp.classCode, max(temp.createTime) createTime from studycase_student_grade temp where temp.teacherEmail = 'mytest' and temp.invalid = 1 group by temp.classCode) t

b.原来的表中找到刚才得道最大记录的表的记录，在根据order by进行排序

select             ssg.schoolId, ssg.classCode, ssg.className, ssg.teacherEmail,ssg.teacherName,ssg.studentName,ssg.createTimefrom studycase_student_grade ssg  inner join(select  temp.classCode, max(temp.createTime) createTime from studycase_student_grade temp where temp.teacherEmail                  = 'mytest' and temp.invalid = 1 group by temp.classCode) ton                ssg.classCode = t.classCodeand                ssg.createTime = t.createTimeand                ssg.teacherEmail = 'mytest'and                ssg.invalid = 1 order by                 ssg.createTime desc

总结：

a.group by 分组后不能添加非分组列，若添加，则分分组列的值为分组后的第一个列的数值

b.group by 分组通常和函数结合使用作为计算，可以有过滤条件，条件判断也是通过函数。