MySQL--Group by分组与count计数（进阶）

分手后的思念是犯贱 2023-08-17 16:03 169阅读 0赞

### MySQL--Group by分组与count计数（进阶） ###

*   *   *  1、Group by语法
         *  2、创建表格
         *  3、题目代码部分
         *  4、文末彩蛋--开心一刻

**更多关于数据库知识请加关注哟~~。若需联系和想安装MySQL请加博主:**  
      **QQ:3327908431**  
      **微信：ZDSL1542334210**

前言：MySQL一系列操作猛如虎，代码易懂清晰，结构也简单，今天我们来谈一谈MySQL里面的一些常用操作：group by分组与count计数（此处难度稍大）。我们依然以例子来讲起。

### 1、Group by语法 ###

Group by的语法："Group by <字段>“意为按照字段进行分类汇总。这里需要注意四点：  
        (1)按照你的分类要求Group by 后字段里没有重复的记录出现；  
        (2)如果Group by 后需要跟上条件进行判断，则：“Group by <字段> having <条件>”；  
        (3)Group by 后面的再判断条件不能用"where”；  
        (4)Group by <字段> 在分组条件不指定的情况下，是默认取每一类的第一条记录作为输出结果。  
        具体情况来看例子：

### 2、创建表格 ###

**学生得分表scores**

create table scores (s_id varchar(5),
        c_id varchar(3),score float);
        
    insert into score values 
    ("001","01",135),
    ("005","01",120),
    ("003","01",110),
    ("002","01",90),
    ("005","02",140),
    ("001","02",125.5),
    ("004","02",100),
    ("006","02",90),
    ("002","03",102),
    ("005","03",100.6),
    ("001","03",100),
    ("003","03",95.6),
    ("004","03",83),
    ("003","02",80),
    ("006","03",79.5);

**学生信息表stu**

create table stu(s_id varchar(5),
       s_name varchar(5),
       s_sex varchar(1),
       s_age int(3),s_day date);
    
    insert into stu values
    ("001","李华","男",23,'1996-8-16'),
    ("002","王二","男",24,'1997-3-16'),
    ("003","赵敏","女",23,'1990-5-26'),
    ("004","张莹莹","女",22,'1995-2-16'),
    ("005","朱亚军","男",25,'1999-8-16'),
    ("006","马云","男",28,'1993-12-16'),
    ("007","周星星","男",23,'1996-7-19');

### 3、题目代码部分 ###

**题目一：查询每门课程得分小于90分的学生数**

select c_id,count(*) from scores 
        where score<90 group by c_id;
    # 答案：
    02	1
    03	2
    # 这里必须先判断分数后再分组，不然分组后就有可能了，没法判断

**题目二：对每门课求平均分**

select c_id,avg(score) from scores group by c_id;
    # 答案：
    01	113.7500
    02	107.2000
    03	93.6667

**题目三：查询每门课程被重修的学生数超过一人的课程与人数**

select c_id,count(*) 重修人数 from scores where
       score<90 group by c_id having 重修人数>1;
    # 答案： 03 2

这里前面的先判断条件在group by 前面，用where，后再判断重修人数大于1就再group by 后，只能用having。

**题目四：查询每位同学的学号、平均成绩**

select s_id,avg(score)平均成绩 from scores group by s_id;
    # 答案 ：
    001	120.3333
    002	96.0000
    003	95.3333
    004	91.5000
    005	120.3333
    006	85.0000

**题目五：查询每门课程的课程号、平均成绩**

select c_id,avg(score)课程平均成绩 from scores group by c_id;
    # 答案 ： 
    01	113.75
    02	107.20
    03	93.67

**题目六：查询90后学生的男女数量，并按照数量降序排列**

select s_sex,count(*)数量 from stu where 
       year(s_bothday)>=1990 group by s_sex order by 数量 desc;
    # 答案 ： 
    男	4
    女	2

**题目七：查询不同性别在各年份的出生人数**

select s_sex 性别,year(s_bothday)年份,count(*)人数 
        from stu group by 年份;
        
    select s_sex 性别,year(s_bothday) 年份,count(*)人数 
        from stu group by s_sex,year(s_bothday);
    # 以上两种方法都对：
    女	1990	1
    男	1993	1
    女	1995	1
    男	1996	2
    男	1997	1
    男	1999	1

这里若不知道时间的转换的朋友请观看：  
[MySQL–时间与日期的处理、获取当地时间、时间与时间戳转换][MySQL]

剧情需要，我们在stu表里面插入一条记录：

insert into stu values
        ("008","周星星","男",27,'1990-7-19');

**题目八：查询同名同姓学生名单，并统计有同名的学生姓名和同名人数**

select *,count(*)同名人数 from stu group by
         s_name having 同名人数>1;  #法一
         
    select *,count(*)人数 from stu where s_name in select 
        (s_name from stu group by s_name having count(*)>1);  #法二
    # 答案：
    007	周星星	男	23	1996-7-19	2

**题目九：查询每门课程的平均成绩，并且平均成绩大于等于100分**

select c_id,avg(score)平均成绩 from scores group by
         c_id having 平均成绩>100;
    # 答案 ：
    01	113.7500
    02	107.2000

**题目十：查询每位同学的平均成绩，且平均分数大于等于100**

select s_id,avg(score)平均成绩 from scores group by
         s_id having 平均成绩>=100;
    # 答案 ：
    001	120.3333
    005	120.3333

**题目十一：查询至少选修了2门课的学生学号**

select s_id from scores group by s_id having
        count(*)>=2;
    # 答案 ：
    001
    002
    003
    004
    005
    006

**题目十二：查询至少两门课程及格的学生学号**

select s_id from scores where score>=90 group by
        s_id having count(*)>=2; # 法一
    
    select s_id from 
        (select * from scores where score >=90)as a1 
        group by a1.s_id having count(*)>=2; # 法二
    # 答案 ： 
    001
    002
    003
    005

### 4、文末彩蛋–开心一刻 ###

**以前我朋友小熊在一家快递公司上班，有一天刚好周末，我就寻思着抽个空去看看他，结果刚到才坐下来，一位美女就拿着手机气冲冲的跑过来说到：“姓熊的，为什么我的快递还没有发货，你是不是没有寄？是不是？你是没有寄吧？”。然后我说对对对，他没有，然后小熊说：“啊，对不起美女，我忘了给你检查了，要不你现在随我进来检查检查，看看有没有？”。然后美女说到：“算了吧，你这速度，有也和没有差不多”，接着她就走了，然后小熊说道：“熟人就是不好做生意！”…**

**博友们好，今天到这里就结束啦每篇文章都有最后的文末彩蛋–轻松一刻哟感谢观看，我是Jetuser-data\[学习更多MySQL知识请加关注\]**  
**链接: \[https://blog.csdn.net/L1542334210\]**  
**CSND:L1542334210**  
![在这里插入图片描述][20190928205144631.png]  
**祝大家工作顺利！阖家欢乐！**

[MySQL]: https://blog.csdn.net/L1542334210/article/details/101473793
[20190928205144631.png]: /images/20230810/d52c64eb88e1473fadab3e6e64d620d6.png