关于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 分组通常和函数结合使用作为计算,可以有过滤条件,条件判断也是通过函数。
展开阅读全文

没有更多推荐了,返回首页