开启掘金成长之旅！这是我参与「掘金日新计划 · 2 月更文挑战」的第 23 天，点击查看活动详情

分组数据

今天主要记录了如何分组数据，汇总表中内容的子集。

涉及到的 select 子句包含：

group by 子句
having 子句

group by

在之前的笔记中，所有的计算都是在表中所有数据上进行的（或者匹配特定的 where 子句）。

但是，有时我们需要将数据分为多个逻辑组，以便对每个组进行聚集计算。

举一个例子：

select
    brand_id,
    count(*) as num_prods
from
    products
group by
    brand_id;

可以看到，上述例子中：

brand_id 包含了品牌的 ID
num_prods 为计算字段
group by 子句指示 MySQL 按照 brand_id 排序并分组数据（对每个 brand_id 计算 num_prods一次）

group by 子句指示 MySQL 分组数据，并对每个组进行聚集（而不是对整个结果集进行聚集）。

注意事项

在使用 group by 子句时，必须注意以下几点：

group by 可以包含任意数目的列。
如果在 group by 子句中嵌套了分组，那么数据将在最后规定的分组上进行汇总。
group by 子句中指定的列必须是检索列或者是有效的表达式（不能是聚集函数）
如果在 select 语句中使用了表达式，那么 group by 中必须指定相同的表达式，不能使用别名。
除了聚集计算语句之外， select 语句中的每个列都必须在 group by 子句中给出。
如果分组列中具有 NULL 值，则 NULL 将作为一个分组返回（如果有多行 NULL 值，则分为同一组）。
group 子句必须出现在 where 子句之后，order by 子句之前。

使用 rollup
使用 rollup 关键字，可以得到每个分组以及每个分组汇总级别（针对每个分组）的值
比如：
select
    brand_id,
    count(*) as num_prods
from
    products
group by
    brand_id with rollup;

having

在 MySQL 中，还可以过滤分组（比如包含哪些分组，排除哪些分组）。

我们知道，where 子句可以对返回的结果集进行过滤，但是，where 子句过滤指定的是行而不是分组（where 子句没有分组的概念）。

为了对分组后的数据进行过滤，MySQL 提供了 having子句

having 子句类似于 where 子句，不同在于，where 子句过滤行，而having 子句过滤分组。注意，having 子句支持所有 where操作符（他们的句法是相同的）。

举个例子，在之前的 select 语句最后，我们添加上 having 子句：

select
    brand_id,
    count(*) as num_prods
from
    products
group by
    brand_id
having
    count(*) >= 2;

在上述语句中，having 子句用于过滤出两个以上的产品数量的品牌分组。同时我们可以看到，having 子句的过滤作用是基于分组聚集值，而不是特定的行值。

我们可以理解为：

where 子句在数据分组之前进行过滤

having 子句在数据分组之后进行过滤

因此，where 子句排除的行不包括在分组中，where 子句排除掉的数据可能会影响到having 子句的分组结果

事实上，我们可以在一条 select 语句中同时使用 where 子句和 having 子句。

比如：

select
    brand_id,
    count(*) as num_prods
from
    products
where
    price >= 500
group by
    brand_id
having
    count(*) >= 10;

在上述语句中：

where 子句过滤出所有价格在 500 以上的物品
然后，group by 子句指示 MySQL 按照 brand_id 进行分组
最后 having 子句过滤出数量大于 10 的品牌的商品总数。

分组和排序

我们发现，使用 group by 分组的数据会以分组的顺序输出，但有时却不是这样（这并不是 SQL 规范要求的）。另外，有时用户也会要求以不同于分组的顺序进行排序。

因此，我们应该提供明确的 order by 子句，这样才可以保证数据按照我们想要的方式进行排序（不要依赖 group by 排序数据）。

比如：

select
    order_id,
    sum(quantity * item_price) as total_price
from
    orderitems
group by
    order_id
having
    sum(quantity * item_price) >= 100
order by
    total_price;

上述语句会返回订单总价格大于或等于 100 的订单号 order_id 以及每项订单的总价格total_price 。

默认情况下可能按照订单号 order_id 来排序，但是使用了 order by 子句之后，返回的结果就是按照总计订单价格 total_price 来排序输出的。

回顾：select子句顺序

到此，我们记录过的 select 子句有很多，在使用时，需要注意使用次序。

下面列出了 select子句在使用时必须遵循的次序：

select：要返回的列（或者表达式）
from：从中检索数据的表
where：行级过滤（对返回的结果集进行过滤）
group by：分组说明（仅在按组计算聚集时使用）
having：组级过滤（在数据分组之后进行过滤）
order by：输出排序顺序
limit：要检索的行数

【MySQL必知必会】：数据分组（group by、having）