课时17 第十二讲分门别类 —— GROUP BY子句14:08

JiaqiFan42

Programmer

课时17 第十二讲分门别类 —— GROUP BY子句14:08

上节课在讲解完聚集函数之后我们提出了一个问题，假如老板想知道的是我们所有会员每一个人分别打了多少钱该怎么做？

只用上节课讲的也可以做，就是张三李四一个一个的求：

SELECT SUM(打赏金额)
FROM income 
WHERE 账号 = ‘张三’;
SELECT SUM(打赏金额)
FROM income 
WHERE 账号 = ‘李四’;
……

但这样太麻烦

对于这个问题，SQL 绐我们提供了一个简单的处理模式——先分组再统计

GROUP BY 分组

分组用到的关键字是 GROUP BY

GROUP BY + 字段等：按照指定字段（或计算字段、函数等）对记录进行分组

比如：

SELECT * 
FROM income
GROUP BY fans_id;

意思就是说把所有打赏记录按照粉丝的账号进行分组，每一组，也就是每个账号只保留一条记录

表中用户总共有31个不同的名字，就被分成了31组，所以这张本来有101条记录的表，运行之后得到的结果里就只有31条记录

当然每一组中，比如 ”我张三翻身了！“ 这一组用户里面，具体显示的是哪一条记录这就是由数据库软件自己决定了

这样看 GROUP BY 似乎和 DISTINCT 很类似，但 GROUP BY 更大的作用是：可以配合聚集函数，对每一组数据分别进行统计

配合聚集函数

例如：

SELECT 
    fans_id,
    SUM(money) AS 打赏金额
FROM income
GROUP BY fans_id
ORDER BY 打赏金额 DESC;

意思就变成了先按照粉丝的账号进行分组 GROUP BY fans_id，然后每一组汇集成一条记录，这个记录里面除了有 fans_id 粉丝账号，还有这个组里面该粉丝的多条打款记录的打赏金额的汇总结果 SUM(money)，别名为 ”打赏金额“

换句话说就是：先分组，然后对每一个组分别用一次统计函数

这次运行之后我们还是31条记录，仍然是每个账号一个组，但这次每个组里还显示出这个账号它一共花了多少钱

为了看得更清晰，可以再对这31条分组汇总记录进行排序，用 ORDER BY 按照打赏总额从大到小排序，得到 ”粉丝土豪榜“

再次注意：ORDER BY 能使用 SELECT 中的列别名是因为执行顺序在 SELECT 之后

注意 SQL 子句之间的书写顺序，顺序写错将报错：

SELECT
FROM
WHERE
GROUP BY
ORDER BY

GROUP BY 在 WHERE 之后，ORDER BY 之前

GROUP BY 计算字段

新任务：老板想知道每个月的打赏情况

思路：用 MONTH() 提取出打赏时间的月份数并作为分组依据

SELECT 
    MONTH(pay_time) AS 月份,
    COUNT(*) AS 该月打赏次数,
    SUM(money) AS 该月打赏总额
FROM income
GROUP BY MONTH(pay_time);

可见，GROUP BY 不仅可以按照字段进行分组，也可以按照函数计算结果去分组

先分组再聚集

但注意，并不是所有函数都可以放到GROUP BY里面的，比如，不能使用count、sum等聚集函数作为分组依据：

SELECT 
    MONTH(pay_time) AS 月份,
    COUNT(*) AS 该月打赏次数,
    SUM(money) AS 该月打赏总额
FROM income
【GROUP BY COUNT(pay_time)】;

结果报错

这是因为，只有先完成分组操作，才能对各组进行聚集统计。

SQL的执行过程是：

先用GROUP BY进行分组
然后再对每个组使用聚集函数如 COUNT 等做统计

因此如果我们反过来，先写聚集函数比如 Count，然后再拿它 GROUP BY ，就会出错

GROUP BY 别名

另一个问题是：GROUP BY 能不能像 ORDER BY 那样使用 SELECT 里的别名呢？

SELECT 
    MONTH(pay_time) AS 月份,
    COUNT(*) AS 该月打赏次数,
    SUM(money) AS 该月打赏总额
FROM income
GROUP BY 月份;

发现运行顺利，没有问题，可以这样用

但注意：本语句只 MySQL 中得到支持，标准SQL语法中，不允许 GROUP BY 后面使用 SELECT 中定义的别名！

大家换了其他主流数据库比如 Sql Server、Oracle 等等，你在 GROUP BY 里面用别名就会出错根本不运行

为什么会这样，道理还出在SQL语句的执行过程上

我们已经学过，SQL语句的执行过程是：

先执行 FROM 表名
然后如果有 WHERE，就执行 WHERE 进行筛选
在执行完 WHERE 条件子句之后，就会执行 GROUP BY 这个分组操作
而执行完 GROUP BY 分完组之后才会去执行 SELECT

所以在执行 GROUP BY 的时候 SELECT 的语句还没有被执行过这些别名还没有生效呢，当然我们在 GROUP BY 里面就不能用它了，至于MySQL为什么可以这么用呢，那是因为 MySQL 对这个查询语法进行了一个特殊的强化，所以人家软件里面绐我们开了一个后门我们才可以使用它，算是 MySQL 里的专享特权吧

但建议大家在写 GROUP BY 的时候，最好还是把计算字段函数等等老老实实写一遍，免得在 MySQL 里面养成了这个写别名的习惯，结果将来换到其他数据库的时候发现不能用

无关字段

事实上 MySQL 给我们开的后门还不仅仅是可以在 GROUP BY 里面使用别名，它还有另外一个我们刚才已经见过的一个特殊效果：在使用 GROUP BY 分组以后，仍然可以在 SELECT 中选择既不是分组依据也不是聚集函数的无关字段

例如，在按照粉丝分组后，除了得到每组的粉丝名和该粉丝的打赏总额，我们再增加一个”礼物级别“字段：

SELECT
 fans_id,
 SUM(money),
 【gift_level】
FROM income
GROUP BY fans_id;

因为粉丝名是分组依据，所以同一组的粉丝名是一致的，而聚集函数算出的也正是该粉丝的打赏总额，这两个字段放在结果中都没有问题。

但是，每一组的打赏记录中，礼物级别是五花八门的，所以最后随机选出一个礼物级别放在每组的一行结果中，这是毫无意义的

标准 SQL 语法规定∶

如果使用了GROUP BY，则SELECT子句中只能出现 GROUP BY 的分组依据字段以及聚集函数

MySQL 比较宽容，进行了特别设计，因而不受此规则限制，虽然它也知道这样做毫无意义（其它数据库会直接报错）

小结：对应 GROUP BY 分组，MySQL 里的两个特别 “待遇”∶

允许 GROUP BY 中使用别名（最好不要，养成坏习惯）
允许在 SELECT 中使用与分组无关而且也不是聚集函数的字段（毫无意义）（其实下一节可以看到，MySQL 留这个后门并非毫无意义）

GROUP BY 多个字段

新任务：想查的不是每一个人一共打了多少钱，而是每一个人在每一种礼物上打了多少钱，比如张三在玫瑰花了多少？或者张三咖啡上又花了多少？该怎么办呢？

换句话说，怎样按照 “人名+礼物” 分组？即将同一个人并且同一种礼物的记录分为一组进行统计？

这就要用到 多层（嵌套）分组 ，把原始数据先按照名字进行一次分组，然后再把每一个大组里面按照礼物进行第二次分成更小的组

多层分组用 GROUP BY 实现非常简单，直接把这些分组依据按照他们的先后顺序用逗号隔开就行：

SELECT 
    fans_id,
    gift_level,
    SUM(money) AS 打赏总额
FROM income

课时17 第十二讲 分门别类 —— GROUP BY子句14:08

课时17 第十二讲分门别类 —— GROUP BY子句14:08