sql中group by的使用

group by：分组，根据by的字段分成这一小类。如：group by id，表示将id相同的放一类。
！注意：group by常搭配聚合函数使用，一般给哪个字段分组就select哪个字段。

1.简单使用group by

表名为“paper”，列：类别class、数量count、摘要context

select 类别, sum(数量) as 数量之和
from 表名
group by 类别；

select class,sum(count) as amount from paper group by class;

返回结果如下表:

2. group by搭配order by

select 类别, sum(数量) AS 数量之和
from A
group by 类别
order by 数量之和 desc；

select class,sum(count) as amount from paper group by class order by amount desc;

返回结果如下表:

3.Having与Where的区别

• where子句的作用是在对查询结果进行分组前，将不符合where条件的行去掉，即在分组之前过滤数据，where条件中不能包含聚组函数，使用where条件过滤出特定的行。

• having 子句的作用是筛选满足条件的组，即在分组之后过滤数据，条件中经常包含聚组函数，使用having条件过滤出特定的组，也可以使用多个分组标准进行分组。

select 类别, sum(数量) as 数量之和 from 表名
group by 类别
having sum(数量) > 18

select class,sum(count) as amount from paper group by class having amount > 18;

Having和Where的联合使用

select class,sum(count) as amount from paper where class = "a" group by class having amount > 18;