SUM()求和函数
SUM() 函数返回数值列的总和。
语法:
SELECT SUM(column_name) FROM table_name;
column_name:字段名(必须是数值字段)
table_name:表名
示例:
查询每位同学的总分:
SELECT a.id,name,SUM(score),clazz
FROM score AS a
INNER JOIN students AS b
ON a.id =b.id
GROUP BY a.id;
SUM()求和函数一班与 GROUP BY 同用,用于组内求和。
HAVING 子句
对上题再添加一个条件:
查询理科六班每位同学的总分:
添加一个限制条件为立刻六班,那是不是只需要加个 WHERE 字句,我们来尝试一下:
SELECT a.id,name,SUM(score),clazz
FROM score AS a
INNER JOIN students AS b
ON a.id =b.id
GROUP BY a.id
WHERE clazz = '理科六班';
很明显,出现报错情况, WHERE clazz = ‘理科六班’ 这条语句出现问题
这正是因为WHERE 关键字无法与聚合函数一起使用,这时便需要 HAVING 子句
HAVING 子句的作用对分组后的各组数据进行过滤操作。
语法:
SELECT column1, aggregate_function(column2)
FROM table_name
GROUP BY column1
HAVING condition;
column1:要检索的列。
aggregate_function(column2):一个聚合函数,例如SUM、COUNT、AVG等,应用于column2的值。
table_name:要从中检索数据的表。
GROUP BY column1:根据column1列的值对数据进行分组。
HAVING condition:一个条件,用于筛选分组的结果。只有满足条件的分组会包含在结果集中。
所以上题正确写法:
将 WHERE 字句改写为 HAVING 字句,或者将 WHERE 字句放到 GROUP BY 字句前面:
正确写法一:
SELECT a.id,name,SUM(score) AS sum,clazz
FROM score AS a
INNER JOIN students AS b
ON a.id =b.id
WHERE clazz = '理科六班'
GROUP BY a.id;
正确写法二:
SELECT a.id,name,SUM(score) AS sum,clazz
FROM score AS a
INNER JOIN students AS b
ON a.id =b.id
GROUP BY a.id
HAVING clazz = '理科六班';
如果一个分组查询同时需要两个过滤条件怎么办?
查询理科六班的总分大于500分的每位同学的总分:
正确写法一:(HAVING…AND…)
SELECT a.id,name,SUM(score) AS sum,clazz
FROM score AS a
INNER JOIN students AS b
ON a.id =b.id
GROUP BY a.id
HAVING clazz = '理科六班' AND sum > 500;
正确写法二:(WHERE…GROUP BY…HAVING)
SELECT a.id,name,SUM(score) AS sum,clazz
FROM score AS a
INNER JOIN students AS b
ON a.id =b.id
WHERE clazz = '理科六班'
GROUP BY a.id
HAVING sum > 500;
本专栏将持续更新,对数据库SQL感兴趣的同学可以关注一下,谢谢!