sql 降序_SQL系列之汇总分析

一、汇总分析

图1 常见的汇总函数

1.1 COUNT函数（忽略Null值）

SELECT COUNT(列名)
FROM 表名;
-- COUNT（*）不忽略NULL值
SELECT COUNT(*)
FROM 表名;
-- 去掉重复值
SELECT COUNT(DISTINCT 列名)
FROM 表名;

1.2 SUM函数

-- 对某列数据求和，只能对数值类型的列计算
SELECT SUM(列名)
FROM 表名;

1.3 AVG函数

-- 对某列数据求平均，只能对数值类型的列计算
SELECT AVG(列名)
FROM 表名;

1.4 MAX,MIN函数

-- 求某列数据的最大值，最小值
SELECT MAX(列名),MIN(列名)
FROM 表名;

二、分组（GROUP BY）

2.1 SQL书写顺序

SELECT 列名
FROM 表名
WHERE 行筛选条件
GROUP BY 列名；

2.2 SQL运行顺序

-- SQL运行顺序
④SELECT 列名
①FROM 表名
②WHERE 筛选条件
③GROUP BY 列名；

1. FROM 从哪个表中查找数据
2. WHERE 查询出符合条件的行
3. GROUP BY 对查出的数据分组
4. SELECT 对分组后的每个组应用函数+组合结果

三、对分组结果指定条件（HAVING）

-- 序号代表运行顺序
⑤SELECT 列名
①FROM 表名
②WHERE 行筛选条件
③GROUP BY 列名
④HAVING 分组的指定条件；

四、 用SQL解决业务问题

4.1 用SQL解决业务问题思路

• 理解清楚业务问题（找出关键点，翻译为大白话）
• 捋顺分析思路
• 写出对应的SQL子句

比如计算各科的平均成绩（和上面思路对应）：

• 计算每门课程的平均成绩
• 分析思路

SELECT 查询结果
       [每门课的课程号：分组，平均成绩：AVG(成绩)]
FROM 从哪张表中查找数据[成绩表：score]
WHERE 查询条件[无]
GROUP BY 分组[每门课程：按照课程号分组]
HAVING 指定分组条件[没有];

• 对应的SQL语句

SELECT 课程号,AVG(成绩)
FROM score
GROUP BY 课程号；

五、对查询结果排序（ORDER BY）

• 降序（DESC）：从大到小
• 升序（ASC）：从小到大（默认）
• 空值（null）排序在最前面

如果数据库的字符集编码是utf-8，汉字排序并不是按照字母顺序。如果编码是gbk，汉字排序是按照字母顺序。

5.1 从查询结果中取出指定行：LIMIT

-- 从查询结果中取出前2行数据
SELECT *
FROM 表名
LIMIT 2;

5.2 SQL运行顺序

⑤SELECT 查询结果
①FROM 从哪张表中查找数据
②WHERE 查询条件
③GROUP BY 分组
④HAVING 对分组结果指定条件
⑥ORDER BY 对查询结果排序
⑦LIMIT 从查询结果中取出指定行

六、看懂报错信息

图1 学会看报错信息

常见错误（每个数据库规则不同，有的支持这个语法，有的却不支持，MySQL能运行就说明支持）

6.1 在GROUP BY中使用了SELECT里的别名

从运行顺序那里可知，GROUP BY子句在SELECT子句前运行，别名是在SELECT子句中设置的，GROUP BY还不知道，所以会报错（有的不会报错）。

6.2 在WHERE子句中使用汇总函数

WHERE是行筛选条件，汇总函数的对象是列。汇总函数要对全列数据进行计算，因而使用它的前提是：结果集已经确定！而WHERE子句还处于“确定”结果集的过程中，因而不能同时使用，HAVING子句能。

6.3 字符串类型的数字

字符串类型和数据类型的排序方式不同，使用时要注意。

七、案例题解析

原始表格

图2 查询参考表格

7.1 汇总分析

查询已选课程的学生人数

/*
大白话：查询有多少人选了课程
SELECT 学号，去掉score表中重复的学号
*/
SELECT COUNT(DISTINCT 学号)
FROM score;

图2 查询结果

7.2 分组

（1）查询各科成绩最高和最低的分数，以课程号，最高分，最低分显示。

/*
分析思路
SELECT 查询结果 [课程号，最高分：MAX（成绩），最低分：MIN（成绩）]
FROM 从哪张表中查找数据 [成绩表score]
WHERE 查询条件 [没有]
GROUP BY 分组[各科成绩：每门课程的成绩，按课程号分组]
*/
SELECT 课程号,MAX(成绩),MIN(成绩)
FROM score
GROUP BY 课程号;

（2）查询每门课程被选修的学生数

/*
分析思路
SELECT 查询结果 [课程号，选修该课程的学生数：COUNT]
FROM 从哪张表中查找数据 [成绩表score]
WHERE 查询条件 [没有]
GROUP BY 分组[每门课程：按课程号分组];
*/
SELECT 课程号,COUNT(学号)
FROM score
GROUP BY 课程号;

7.3 分组结果的条件

（1）查询平均成绩大于60分学生的学号和平均成绩

/*
通俗翻译：
平均成绩：计算每个学生的平均成绩
涉及“每个”就是要分组了
对分组结果指定条件：平均成绩大于60分

分析思路
SELECT 查询结果 [学号，平均成绩：AVG]
FROM 从哪张表中查找数据 [成绩表score]
WHERE 查询条件 [没有]
GROUP BY 分组[按学号分组]
HAVING 对分组结果指定条件[平均值大于60]
*/
SELECT 学号, AVG(成绩)
FROM score
GROUP BY 学号
HAVING AVG(成绩)>60;

（2）查询至少选修两门课程的学生学号

/*
问题解析：
第一步：计算出每个学生的选课数量，按学号分组
第二步：至少两门选修课：对应每个学生的选修课程数目>=2，对分组结果指定条件

分析思路
SELECT 查询结果 [学号，每个学生选课数目：COUNT]
FROM 从哪张表中查找数据 [课程的学生学号：score]
WHERE 查询条件 [至少选修两门课程，要用分组计算每个学生的选课数量，用不到WHERE子句]
GROUP BY 分组[按学号分组，因为是统计每个学生]
HAVING 对分组结果指定条件[至少选修两门课程]
*/
SELECT 学号, COUNT(课程号) AS 选修课程数目
FROM score
GROUP BY 学号
HAVING COUNT(课程号)>=2;

（3）查询同名同姓学生名单并统计同名人数

/*
问题解析：
第一步：找出姓名相同的学生，每个姓名对应的学生人数
怎么算姓名相同？姓名分组后人数>=2

分析思路
SELECT 查询结果 [姓名，人数：COUNT（*）]
FROM 从哪张表中查找数据 [学生表：student]
WHERE 查询条件 [没有]
GROUP BY 分组[姓名相同：按姓名分组]
HAVING 对分组结果指定条件[姓名相同：COUNT(*)>=2]
*/
SELECT 姓名, COUNT(*) AS 人数
FROM student
GROUP BY 姓名
HAVING COUNT(*)>=2;

7.4 对查询结果排序

（1）查询不及格的课程并按课程号从大到小排列

/*
分析思路
SELECT 查询结果 [课程号]
FROM 从哪张表中查找数据 [课程号：score]
WHERE 查询条件 [不及格：成绩<60]
GROUP BY 分组[没有]
HAVING 对分组结果指定条件[没有]
ORDER BY 对查询结果排序[课程号从大到小：降序（DESC）]
*/
SELECT 课程号
FROM score
WHERE 成绩<60
ORDER BY 课程号 DESC;

（2）查询每门课程的平均成绩，结果按平均成绩升序排序，平均成绩相同时，按课程号降序排序

/*
分析思路
SELECT 查询结果 [课程号，平均成绩：AVG]
FROM 从哪张表中查找数据 [score]
WHERE 查询条件 []
GROUP BY 分组[课程号：因为是每门课程]
HAVING 对分组结果指定条件[没有]
ORDER BY 对查询结果排序[平均成绩升序（ASC），平均成绩相同时，按课程号降序排序（DESC）]
*/
SELECT 课程号, AVG(成绩) AS 平均成绩
FROM score
GROUP BY 课程号
ORDER BY 平均成绩 ASC, 课程号 DESC;

（3）检索课程号为“0004”且分数小于60的学生学号，结果按分数降序排序

/*
分析思路
SELECT 查询结果 [学号]
FROM 从哪张表中查找数据 [score]
WHERE 查询条件 [课程号=“0004”且成绩小于60分]
GROUP BY 分组[无]
HAVING 对分组结果指定条件[没有]
ORDER BY 对查询结果排序[成绩降序（DESC）]
*/
SELECT 学号
FROM score
WHERE 课程号='0004' AND 成绩<60
ORDER BY 成绩 DESC;

（4）统计每门课程的学生选修人数（超过2人的课程才统计），要求输出课程和选修人数，查询结果按人数降序排序，若人数相同，按课程号升序排序

/*
分析思路
SELECT 查询结果 [课程号，选修人数（学号）：COUNT]
FROM 从哪张表中查找数据 [score]
WHERE 查询条件 []
GROUP BY 分组[课程号：因为是每门课程]
HAVING 对分组结果指定条件[超过2人的课程]
ORDER BY 对查询结果排序[选修人数降序（DESC），选修人数相同时，按课程号升序排序（ASC）]
*/
SELECT 课程号, COUNT(学号) AS 选修人数
FROM score
GROUP BY 课程号
HAVING COUNT(学号)>2
ORDER BY 选修人数 DESC, 课程号 ASC;

（5）查询两门以上不及格课程学生的学号和不及格课程的平均成绩

/*
分解题目：
1）[两门以上][不及格课程]是限制条件
2）[学生学号和平均成绩]，是每个学生的平均成绩，显示学号，平均成绩
过程：
第一步：得到每个学生的平均成绩：学号，平均成绩
    SELECT 查询结果 [学号，平均成绩：AVG]
    FROM 从哪张表中查找数据 [score]
    WHERE 查询条件 [无]
    GROUP BY 分组[每个学生，所以按学号分组]
    HAVING 对分组结果指定条件[无]
    ORDER BY 对查询结果排序[无]
第二步：加上限制条件：[两门以上]+[不及格课程]
    SELECT 查询结果 [课程号：COUNT]
    FROM 从哪张表中查找数据 [score]
    WHERE 查询条件 [成绩<60]
    GROUP BY 分组[每个学生，所以按学号分组]
    HAVING 对分组结果指定条件[课程数目>2]
    ORDER BY 对查询结果排序[无]
*/
SELECT 学号, AVG(成绩) AS 平均成绩, COUNT(课程号)
FROM score
WHERE 成绩<60
GROUP BY 学号
HAVING COUNT(课程号)>2;

八、感想

初次尝试写稍微复杂的SQL语句，对我来说还是很吃力的。整个过程就是看不懂题 ->题懂了，不知道从哪入手 -> 能想到SQL框架，但是子句细节处不会写。特别是WHERE和HAVING，经常不知道用哪个。

唯一的解决办法就是练习！练习！练习！

end.