一、聚集函數(shù)都有哪些

SQL 中的聚集函數(shù)一共包括 5 個，可以幫我們求某列的最大值、最小值和平均值等，它們分別是：

如果我們想要查詢最大生命值大于 6000 的英雄數(shù)量

SELECT COUNT(*) FROM heros WHERE hp_max > 6000;

查詢最大生命值大于 6000，且有次要定位的英雄數(shù)量，需要使用 COUNT 函數(shù)

SELECT COUNT(role_assist) FROM heros WHERE hp_max > 6000;

需要說明的是，有些英雄沒有次要定位，即 role_assist 為 NULL，這時COUNT(role_assist)會忽略值為 NULL 的數(shù)據(jù)行，而 COUNT(*) 只是統(tǒng)計數(shù)據(jù)行數(shù)，不管某個字段是否為 NULL

查詢射手（主要定位或者次要定位是射手）的最大生命值的最大值是多少，需要使用 MAX 函數(shù)

SELECT MAX(hp_max) FROM heros WHERE role_main = '射手' or role_assist = '射手';

想要知道英雄的數(shù)量，我們使用的是 COUNT(*) 函數(shù)，求平均值、最大值、最小值，以及總的防御最大值，我們分別使用的是 AVG、MAX、MIN 和 SUM 函數(shù)。另外我們還需要對英雄的主要定位和次要定位進行篩選，使用的是WHERE role_main = '射手' or role_assist = '射手'

SELECT COUNT(*), AVG(hp_max), MAX(mp_max), MIN(attack_max), SUM(defense_max) FROM heros WHERE role_main = '射手' or role_assist = '射手';

需要說明的是 AVG、MAX、MIN 等聚集函數(shù)會自動忽略值為 NULL 的數(shù)據(jù)行，MAX 和 MIN 函數(shù)也可以用于字符串類型數(shù)據(jù)的統(tǒng)計，如果是英文字母，則按照 A—Z 的順序排列，越往后，數(shù)值越大。如果是漢字則按照全拼拼音進行排列。比如：

SELECT MIN(CONVERT(name USING gbk)), MAX(CONVERT(name USING gbk)) FROM heros;

需要說明的是，我們需要先把 name 字段統(tǒng)一轉化為 gbk 類型，使用CONVERT(name USING gbk)，然后再使用 MIN 和 MAX 取最小值和最大值。

我們也可以對數(shù)據(jù)行中不同的取值進行聚集，先用 DISTINCT 函數(shù)取不同的數(shù)據(jù)，然后再使用聚集函數(shù)。比如我們想要查詢不同的生命最大值的英雄數(shù)量是多少

SELECT COUNT(DISTINCT hp_max) FROM heros;

實際上在 heros 這個數(shù)據(jù)表中，一共有 69 個英雄數(shù)量，生命最大值不一樣的英雄數(shù)量是 61 個

統(tǒng)計不同生命最大值英雄的平均生命最大值，保留小數(shù)點后兩位。首先需要取不同生命最大值，即DISTINCT hp_max，然后針對它們?nèi)∑骄?，即AVG(DISTINCT hp_max)，最后再針對這個值保留小數(shù)點兩位，也就是ROUND(AVG(DISTINCT hp_max), 2)

SELECT ROUND(AVG(DISTINCT hp_max), 2) FROM heros;

二、對數(shù)據(jù)進行分組，并進行聚集統(tǒng)計

按照英雄的主要定位進行分組，并統(tǒng)計每組的英雄數(shù)量

SELECT COUNT(*), role_main FROM heros GROUP BY role_main;

對英雄按照次要定位進行分組，并統(tǒng)計每組英雄的數(shù)量

SELECT COUNT(*), role_assist FROM heros GROUP BY role_assist;

如果字段為 NULL，也會被列為一個分組。在這個查詢統(tǒng)計中，次要定位為 NULL，即只有一個主要定位的英雄是 40 個

可以使用多個字段進行分組，這就相當于把這些字段可能出現(xiàn)的所有的取值情況都進行分組。比如，我們想要按照英雄的主要定位、次要定位進行分組，查看這些英雄的數(shù)量，并按照這些分組的英雄數(shù)量從高到低進行排序

SELECT COUNT(*) as num, role_main, role_assist FROM heros GROUP BY role_main, role_assist ORDER BY num DESC;

三、使用 HAVING 過濾分組，它與 WHERE 的區(qū)別是什么

當我們創(chuàng)建出很多分組的時候，有時候就需要對分組進行過濾。你可能首先會想到 WHERE 子句，實際上過濾分組我們使用的是 HAVING。HAVING 的作用和 WHERE 一樣，都是起到過濾的作用，只不過 WHERE 是用于數(shù)據(jù)行，而 HAVING 則作用于分組

想要按照英雄的主要定位、次要定位進行分組，并且篩選分組中英雄數(shù)量大于 5 的組，最后按照分組中的英雄數(shù)量從高到低進行排序

SELECT COUNT(*) as num, role_main, role_assist FROM heros GROUP BY role_main, role_assist HAVING num > 5 ORDER BY num DESC;

篩選最大生命值大于 6000 的英雄，按照主要定位、次要定位進行分組，并且顯示分組中英雄數(shù)量大于 5 的分組，按照數(shù)量從高到低進行排序

SELECT COUNT(*) as num, role_main, role_assist FROM heros WHERE hp_max > 6000 GROUP BY role_main, role_assist HAVING num > 5 ORDER BY num DESC;

這里我們就需要先使用 WHERE 子句對最大生命值大于 6000 的英雄進行條件過濾，然后再使用 GROUP BY 進行分組，使用 HAVING 進行分組的條件判斷，然后使用 ORDER BY 進行排序