当前位置：首页 > article >正文

10. Mysql 分组或汇总查询

article 2024/11/14 5:11:17

Mysql 函数参考和扩展：Mysql 常用函数和基础查询、 Mysql 官网

Mysql 语法执行顺序如下，一定要清楚！！！运算符相关，可前往 Mysql 基础语法和执行顺序扩展。

(8) select (9) distinct (11)<columns_name list>
(1) from <left_table>
(3) <join_type> join <right_table>
(2) on <join_condition>
(4) where <where_condition>
(5) group by <group_by columns_name list>
(6) with <rollup>
(7) having <having_condition>
(10) order by <order_by columns_name list>
(12) limit <[offset，] rows>
;

1. 数据准备

这里有一张一年级一班的成绩得分表。

create table sql_test1.student_subject_scroe
(
    student_id varchar(255) comment '学生编号',
    subject    varchar(255) comment '课程名称',
    score      int comment '分数'
);
insert into sql_test1.student_subject_scroe (student_id, subject, score) values ('01', 'english', 89);
insert into sql_test1.student_subject_scroe (student_id, subject, score) values ('01', 'math', null);
insert into sql_test1.student_subject_scroe (student_id, subject, score) values ('01', 'china', 97);
insert into sql_test1.student_subject_scroe (student_id, subject, score) values ('02', 'english', 87);
insert into sql_test1.student_subject_scroe (student_id, subject, score) values ('02', 'math', 53);
insert into sql_test1.student_subject_scroe (student_id, subject, score) values ('02', 'china', 96);
insert into sql_test1.student_subject_scroe (student_id, subject, score) values ('03', 'english', 87);
insert into sql_test1.student_subject_scroe (student_id, subject, score) values ('03', 'math', 53);
insert into sql_test1.student_subject_scroe (student_id, subject, score) values ('03', 'china', 96);
insert into sql_test1.student_subject_scroe (student_id, subject, score) values ('04', 'english', 84);
insert into sql_test1.student_subject_scroe (student_id, subject, score) values ('04', 'math', 52);
insert into sql_test1.student_subject_scroe (student_id, subject, score) values ('04', 'china', 96);
insert into sql_test1.student_subject_scroe (student_id, subject, score) values ('05', 'english', 74);
insert into sql_test1.student_subject_scroe (student_id, subject, score) values ('05', 'math', 47);
insert into sql_test1.student_subject_scroe (student_id, subject, score) values ('05', 'china', 92);
insert into sql_test1.student_subject_scroe (student_id, subject, score) values ('06', 'english', 73);
insert into sql_test1.student_subject_scroe (student_id, subject, score) values ('06', 'math', 40);
insert into sql_test1.student_subject_scroe (student_id, subject, score) values ('06', 'china', 90);
insert into sql_test1.student_subject_scroe (student_id, subject, score) values ('07', 'english', 73);
insert into sql_test1.student_subject_scroe (student_id, subject, score) values ('07', 'math', 40);
insert into sql_test1.student_subject_scroe (student_id, subject, score) values ('07', 'china', 90);
insert into sql_test1.student_subject_scroe (student_id, subject, score) values ('08', 'english', 73);
insert into sql_test1.student_subject_scroe (student_id, subject, score) values ('08', 'math', 40);
insert into sql_test1.student_subject_scroe (student_id, subject, score) values ('08', 'china', 90);

2. 汇总查询

输入的是一组数据的集合，输出的是单个值。

常用的聚合函数如下：

count([distinct] expr)：返回expr的记录数。
sum(expr)：返回expr的汇总值。
avg(expr)：返回expr的平均值。
std(expr)：返回expr的标准差。
max(expr)：返回expr的最大值。
min(expr)：返回expr的最小值。
group_concat([distinct] expr …)：返回一串字符串。

统计表数据总量、学生数、有效数据数量和考试科目。

# 统计一年级一班成绩得分表，总记录数、学生人数、有效得分记录数和考试科目
select count(*)                                                      total_records,
       count(distinct student_id)                                    s_cnt,
       count(score)                                                  valid_cnt,
       group_concat(distinct subject order by subject separator '、') subjects
from sql_test1.student_subject_scroe;
+---------------+-------+-----------+------------------------+
| total_records | s_cnt | valid_cnt | subjects               |
+---------------+-------+-----------+------------------------+
|            24 |     8 |        23 | china、english、math   |
+---------------+-------+-----------+------------------------+

count(*)：返回表中数据总量；
count(1)：与COUNT(*)效果相同，因为它只是在每一行中都返回一个非空的值；
count(字段)：返回字段非空值的行数；

count(*)会统计值为 NULL 的行，而count(字段)不会统计此列为 NULL 值的行。

执行效率顺序：count(*)=count(1) >count(字段)

只适用于数值类型的函数有：avg()、sum()、std()；

# 查看一年级一班语文平均分，avg = sum/count
select avg(score)                              china_avg_score,
       sum(score) / count(distinct student_id) china_avg_score2,
       std(score)                              std_score
from sql_test1.student_subject_scroe
where subject = 'china';
+-----------------+------------------+--------------------+
| china_avg_score | china_avg_score2 | std_score          |
+-----------------+------------------+--------------------+
|         93.3750 |          93.3750 | 2.9553976043842236 |
+-----------------+------------------+--------------------+

3. 分组查询

SELECT中出现的非汇总聚合的字段必须声明在GROUP BY 中。

查看一年级一班各学科数据详情。

select subject,
       count(score)                                          valid_cnt,
       avg(score)                                            avg_score,
       sum(score) / count(score)                             avg_score2,
       std(score)                                            std_score,
       min(score)                                            min_score,
       max(score)                                            max_score,
       group_concat(score order by score desc separator '、') score_str
from sql_test1.student_subject_scroe
group by subject;
+---------+-----------+-----------+------------+--------------------+-----------+-----------+---------------------------------------+
| subject | valid_cnt | avg_score | avg_score2 | std_score          | min_score | max_score | score_str                             |
+---------+-----------+-----------+------------+--------------------+-----------+-----------+---------------------------------------+
| china   |         8 |   93.3750 |    93.3750 | 2.9553976043842236 |        90 |        97 | 97、96、96、96、92、90、90、90        |
| english |         8 |   80.0000 |    80.0000 | 6.8738635424337655 |        73 |        89 | 89、87、87、84、74、73、73、73        |
| math    |         7 |   46.4286 |    46.4286 |  5.876275371772324 |        40 |        53 | 53、53、52、47、40、40、40            |
+---------+-----------+-----------+------------+--------------------+-----------+-----------+---------------------------------------+

查看一年级一班学科平均分低于60的学科数据详情

select subject,
       count(score)                                          valid_cnt,
       avg(score)                                            avg_score,
       sum(score) / count(score)                             avg_score2,
       std(score)                                            std_score,
       min(score)                                            min_score,
       max(score)                                            max_score,
       group_concat(score order by score desc separator '、') score_str
from sql_test1.student_subject_scroe
where score is not null
group by subject
having avg(score) < 60;
+---------+-----------+-----------+------------+-------------------+-----------+-----------+----------------------------------+
| subject | valid_cnt | avg_score | avg_score2 | std_score         | min_score | max_score | score_str                        |
+---------+-----------+-----------+------------+-------------------+-----------+-----------+----------------------------------+
| math    |         7 |   46.4286 |    46.4286 | 5.876275371772324 |        40 |        53 | 53、53、52、47、40、40、40       |
+---------+-----------+-----------+------------+-------------------+-----------+-----------+----------------------------------+