Mysql 语句之group by, having, count

Mysql的group by, having, count

平时经常用错，这里举一些使用的例子。

• group by
• having
• 聚合函数count，avg，min，max，sum等

一些基础的sql语句：
http://www.cnblogs.com/xinge1993/p/4769468.html

常用的sql
http://www.cnblogs.com/sunada2005/p/3411873.html

---

group by

根据by后面的字段的值分组，值相同的归为一组。

having

group by后，进行组类的判断。where后面也是跟条件判断，但是where是group by之前（如果有group by的话），针对每一行数据；having是针对group by后的组级别的数据。

count等聚合函数

• count统计条数；
• sum求和；
• avg求平均；
• max最大；
• min最小。

注意：聚合函数并不只和group by一起用，不是只有group by的地方才能用它。
比如： select count(*) from stu; # 统计stu表的条数

示例

1：group、两个表join

create table tmp(rq varchar(10),shengfu nchar(1))

insert into tmp values('2005-05-09','胜')
insert into tmp values('2005-05-09','胜')
insert into tmp values('2005-05-09','负')
insert into tmp values('2005-05-09','负')
insert into tmp values('2005-05-10','胜')
insert into tmp values('2005-05-10','负')
insert into tmp values('2005-05-10','负')

统计：每一天的胜负次数。输出是：rq，胜次数，负次数。

select a.rq, a.sheng, b.fu from (select rq, count(shengfu) sheng from tmp where shengfu='胜' group by rq, shengfu) a
join
(select rq, count(shengfu) fu from tmp where shengfu='负' group by rq, shengfu) b
on a.rq = b.rq

2：每个学生的成绩在90分以上的各有多少门

create table sc (
sno int,
pno varchar(16),
grade int
)

insert into sc values(1, 'YW', 95)
insert into sc values(1, 'SX', 98)
insert into sc values(1, 'YY', 90)
insert into sc values(2, 'YW', 89)
insert into sc values(2, 'SX', 91)
insert into sc values(2, 'YY', 92)
insert into sc values(3, 'YW', 85)
insert into sc values(3, 'SX', 88)
insert into sc values(3, 'YY', 96)
insert into sc values(4, 'YW', 95)
insert into sc values(4, 'SX', 89)
insert into sc values(4, 'YY', 88)

select sno, count(*) from sc where grade>=90 group by sno;

# 刚开始写成这样下面这样，是错误的：
select sno, count(*) from sc group by sno, grade having grade>=90

输出结果：

3：至少有两门课程在90分以上才能有资格，列出有资格的学生号及90分以上的课程数

select sno,count(*) from sc where grade>=90 group by sno having count(*)>=2

4：列出平均成绩大于等于90分并且语文课大于等于95的学生sno和平均成绩

select sno, avg(grade) from sc where sno in (select sno from sc where grade>=95 and pno='YW') group by sno having avg(grade)>=90

# 两种错误的，错误在哪自己分析
select sno from (select * from sc where pno='YW' and grade>=95) group by sno having avg(grade)>=90

select sno, grade from sc where sno in (select sno from sc group by sno having avg(grade)>=90) and pno='YW' and grade>=95 group by sno

5 :平均成绩至少比学号是3的平均成绩高的学生学号以及平均分数

# 两种方法
select sno, avg(grade) from sc group by sno having avg(grade) > (select avg(grade) from sc where sno=3 group by sno)

select sno, avg(grade) from sc group by sno having avg(grade) > (select avg(grade) from sc where sno=3)
#上面这句，having后面的select子查询，用到了聚合函数avg但是没有group by。看出，group by和聚合函数不是天生在一起用的。

6 : 查询每一个班级中年龄大于20且性别为男的人数

这个题来自http://www.cnblogs.com/wang-123/archive/2012/01/05/2312676.html 的最后面。但是，原博客中的sql语句是错误的。

create TABLE Table1
(
    ID int auto_increment primary key,   
    classid int, 
    sex varchar(10),
    age int
) 

insert into Table1(classid, sex, age) values(1,'m',20)
insert into Table1(classid, sex, age) values(2,'f',22)
insert into Table1(classid, sex, age) values(3,'m',23)
insert into Table1(classid, sex, age) values(4,'m',22)
insert into Table1(classid, sex, age) values(1,'m',24)
insert into Table1(classid, sex, age) values(2,'f',19)
insert into Table1(classid, sex, age) values(4,'m',26)
insert into Table1(classid, sex, age) values(1,'m',24)
insert into Table1(classid, sex, age) values(1,'m',20)
insert into Table1(classid, sex, age) values(2,'f',22)
insert into Table1(classid, sex, age) values(3,'m',23)
insert into Table1(classid, sex, age) values(4,'m',22)
insert into Table1(classid, sex, age) values(1,'m',24)
insert into Table1(classid, sex, age) values(2,'f',19)

select COUNT(*) as '>20岁人数', classid from Table1 where sex='m' and age>20 group by classid

# 原博客错误的sql
select COUNT(*) as '>20岁人数', classid from Table1 where sex='m' group by classid,age having age>20 

7 : 班级人数大于等于3人的那些班级的学生

create table stu_class_info(
    sid int,
    class_id int,
    snane varchar(32)
)

insert into stu_class_info values(1, 1, 'zhangsan')
insert into stu_class_info values(2, 1, 'zhangsan')
insert into stu_class_info values(3, 2, 'zhangsan')
insert into stu_class_info values(4, 3, 'zhangsan')
insert into stu_class_info values(5, 2, 'zhangsan')
insert into stu_class_info values(6, 2, 'zhangsan')
insert into stu_class_info values(7, 2, 'zhangsan')
insert into stu_class_info values(8, 4, 'zhangsan')
insert into stu_class_info values(9, 4, 'zhangsan')
insert into stu_class_info values(10, 2, 'zhangsan')

# 班级人数大于等于3人的那些班级的学生
select * from stu_class_info where class_id in (select class_id from stu_class_info group by class_id having count(class_id)>=3) order by class_id

参考链接

http://www.cnblogs.com/xinge1993/p/4769468.html
http://www.cnblogs.com/sunada2005/p/3411873.html
http://blog.csdn.net/ooooooobh/article/details/52795073

---