SQL 之 汇总数据

关键字：DISTINCT , SUM , AVG , MIN , MAX , COUNT , GROUP BY , HAVING

 

也叫聚合函数

 

1. 关键字DISTINCT  消除重复

保证返回的新表每一行都是不相同的。

查询Artist表里面有哪些歌手：

SELECT

DISTINCT

Artist

FROM SongTitles

ORDER BY Artist

 

DISTINCT 总是紧随SELECT之后，表示只会返回SELECT后面的columnlist的列值唯一的那些行。SELECT DISTINCT columnlist FROM table ORDER BY columnlist

查询Artist表里artist和album的唯一组合：

SELECT

DISTINCT

Artist,

Album

FROM SongTitles

ORDER BY Artist, Album

 

2. 聚合函数

区别于标量函数（只对单个的数字或者值进行计算），聚合函数意味着可以用于分组数据（一组数据集合）。常用：COUNT，SUM，AVG，MIN，MAX，对分组数据进行计数，求和，求平均值，求最小值，求最大值

SELECT

SUM (Fee) AS ‘Total Gym Fee’

FROM Fees

WHERE FeeType = ‘Gym’

计算顺序：先由WHERE子句返回符合条件的行，再对这些行求Fee列的全部值的和。

 

其余如AVG, MIN, MAX也是类似。

MySQL里面要求大多数聚合函数右边和左括号之间不能有空格。

SELECT

MIN(Fee) AS ‘Min Gym Fee’,

AVG(Fee) AS ‘Average Gym Fee’

FROM Fees

WHERE FeeType = ‘Gym’

 

3. COUNT函数

（1）COUNT(*)

就是返回选中的行的数目。

SELECT

COUNT (*) AS ‘Count of Homework Rows’

FROM Grades

WHERE GradeType = ‘Homework’

可以理解为SQL检索了WHERE选中的行的所有列，然后返回了行的数目。

 

（2）COUNT(column)

SELECT

COUNT (Grade) AS ‘Count of Homework Scores’

FROM Grades

WHERE GradeType = ‘Homework’

 

PS: 这种格式，当column的值是NULL的时候COUNT不会计算在内。

所以和（1）的区别可能就在于此，COUNT(Grade)的数目会少于或等于GradeType = ‘Homework’的行数，当Grade字段存在某些行出现NULL的情况会少于后者。

 

（3）COUNT (DISTINCT columnlist)

SELECT

COUNT (DISTINCT FeeType) AS ‘Number of Fee Types’

FROM Fees

返回不同的FeeType的个数

 

4. GROUP BY分组数据

使用GROUP BY把SELECT返回的数据分成任意数目的组，根据设置的分组条件。

 

SELECT

GradeType AS ‘Grade Type’,

AVG (Grade) AS ‘Average Grade’

FROM Grades

GROUP BY GradeType

ORDER BY GradeType

 

指定了根据GradeType列的值来创建组：

GradeType有两个值：Homework，Quiz，因此分成两组；第二列是计算分数Grade的平均值，因此结果表会有两行，分别是GradeType为Homework的那些行的Grade平均值，还有GradeType为Quiz的那些行的Grade平均值。

 

Grades表：

GradeID     Student               GradeType     Grade

1                  Susan                  Quiz                   92

2                  Susan                  Quiz                   95

3                  Susan                  Homework        84

4                  Kathy                  Quiz                    62

5                  Kathy                  Quiz                    81

6                  Kathy                  Homework         NULL

7                  Alec                    Quiz                      58

8                  Alec                     Quiz                     74

9                  Alec                     Homework          88

 

结果表：

Grade Type      Average Grade

Homework       86

Quiz                   77

 

PS：使用GROUP BY时，columnlist的所有列要么是GROUP BY子句中的列要么是聚合函数中使用的列。比如：

SELECT

GradeType AS ‘Grade Type’,

AVG (Grade) AS ‘Average Grade’,

Student AS ‘Student’

FROM Grades

GROUP BY GradeType

ORDER BY GradeType

 

则会报错（MySQL不报错但是会返回错误的结果）：Student列既不在GROUP BY子句中，也不在任何聚合函数中，SQL不知如何处理Student列。

 

5. 多列和排序

分组依据可以是多个列。上面的问题可以这样解决：

SELECT

GradeType AS ‘Grade Type’,

AVG (Grade) AS ‘Average Grade’,

Student AS ‘Student’

FROM Grades

GROUP BY GradeType, Student

ORDER BY Student, GradeType

 

PS：不同于ORDER BY子句，GROUP BY子句columnlist的顺序是没有意义的。

结果最后呈现是依据分组的，同一组的在一起，同组之间的顺序是按照ORDER BY子句的顺序排序的。GROUP BY只是创建了分组，仍要ORDER BY来按照正确的顺序显示数据。

结果：

Student      GradeType      Average Grade

Alec            Homework        88

Alec            Quiz                   66

Kathy         Homework        NULL

Kathy         Quiz                    71.5

Susan        Homework        84

Susan        Quiz                   93.5

 

6. 关键字HAVING

HAVING是把某些特定的查询条件分组整组数据。区别于用WHERE和IN，只能对单独的行进行处理。

只想看到分数范围在60到80的行（以分组显示）是可以用WHERE子句来限制的：

SELECT

GradeType AS ‘Grade Type’,

AVG (Grade) AS ‘Average Grade’,

Student AS ‘Student’

FROM Grades

WHERE GradeType = ‘Quiz’

AND Grade BETWEEN 60 AND 80

GROUP BY GradeType, Student

ORDER BY Student, GradeType

 

但是只想看到平均分在60到80分的各组数据就只能用HAVING了：

SELECT

Student AS ‘Student’,

GradeType AS ‘Grade Type’,

AVG (Grade) AS ‘Average Grade’

FROM Grades

WHERE GradeType = ‘Quiz’

GROUP BY Student

HAVING AVG (Grade) BETWEEN 60 AND 80

ORDER BY Student

 

WHERE子句保证只能返回GradeType是’Quiz’的行，HAVING保证你只选择的是平均成绩在60到80之间的学生。

事实上上面这条语句会出错。原因是SELECT GradeType AS‘Grade Type’，GradeType既不出现在GROUP BY子句中也不出现在聚合函数中。所以必须添加到GROUP BY子句中才可以：

SELECT

Student AS ‘Student’,

GradeType AS ‘Grade Type’,

AVG (Grade) AS ‘Average Grade’

FROM Grades

WHERE GradeType = ‘Quiz’

GROUP BY Student, GradeType

HAVING AVG (Grade) BETWEEN 60 AND 80

ORDER BY Student

 

SELECT语句的一般顺序格式：

SELECT columnlist

FROM tablelist

WHERE condition

GROUP BY columnlist

HAVING condition

ORDER BY columnlist

 

HAVING是作用于分组数据的，所以一定在GROUP BY之后，但是在ORDER BY之前。