关于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;

测试数据：

1	f	11	001
2	w	12	001
3	z	12	002
4	l	11	002
5	test	0	003

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

sql1：

select
           ut.classaNo
from 
            user_test ut
group by 
            ut.classaNo

结果：

classaNo
001
002
003

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

介绍一个函数：

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

sql2：

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

结果：

classaNo     group_concat(ut.username)
001             f,w
002             z,l
003             test

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

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

sql3：

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

结果：

classaNo     group_concat(ut.username)   username     age
001             f,w                        f           11
002             z,l                        z           12
003             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 ut
group by 
            ut.classaNo
having 
           count(ut.username) >= 2

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

-- Table "studycase_student_grade" DDL

CREATE 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.createTime
from 
		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) t
on
                ssg.classCode = t.classCode
and
                ssg.createTime = t.createTime
and 
               ssg.teacherEmail = 'mytest'
and 
               ssg.invalid = 1 
order by 

                ssg.createTime desc

总结：

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

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