MySQL之GROUP BY

group by

当前mysql版本8.0.15

1.创建分组

group by根据分组字段将结果集进行分组，可以理解为以分组字段查询出相关结果集，并创建虚拟表，从虚拟表中执行其他条件并返回数据

举例说明:

这是原始表

执行sql: select * from users group by name;

创建的虚拟表
id name status
1 123 1
2 （同上） 3
3 before 1
4 （同上） 2
etc.

由于没有其他条件，但是group by 输出结果是对分组字段与对应结果集为一对一的关系，根据默认顺序，对虚拟表进行合并，
id为1，2的结果合并为1条，根据默认规则，取第一条；id为3，4的同理

输出结果：

对多个字段分组，即对虚拟表取多个分组字段与结果集的一对多关系；
如

注意id为1的记录和id为5的记录，name和status字段一致

执行 select * from users group by name, status;

输出结果：

没有id为5的记录，因为该记录在虚拟表中和id为1的记录在同一组，只输出了一条，即id为1的记录

2.过滤分组

当我们需要对取得虚拟表的数据的过程中进行过滤时，可以使用where
select * from users where name='123' or name='before' group by name;

当我们需要对取得虚拟表的数据后的分组过程中进行过滤时，可以使用having
select * from users group by status having count(*) > 2;

两者的区别：
作用过程，上文所叔；
作用对象，根据上文补充，where作用的是表的行，having作用的是分组；
子句不同，where由于不存在任何结果集，不能使用聚合函数；而having作用于分组结果，通常使用聚合函数做更进一步的分组；

两者可以联合使用，如select * from users where name='123' or name='before' group by name having count(*)>1;

3.排序

对虚拟表的分组结果进行排序，在group by 的最后添加 order by子句；
但是目前我没有找到对虚拟表得到数据前对每一组进行排序的简单方法；这个的实现方式在下文，利用子查询或联合查询实现；
不过如果对虚拟表得到数据前的所有组做统一排序，和对虚拟表的分组结果进行排序是一样的

select * from users where name='123' or name='before' group by name order by id desc;

4.聚合函数
聚合是对结果集的作用，因此where语句不可以使用聚合函数，而having、select后，由于得到了结果集，因此可以使用聚合函数
常用的聚合函数：COUNT(), AVG(),MIN(),MAX()

select id, name, status from users where name='123' or name='before' group by name having max(status);

这句的having其实是失效的，因为having后一定是过滤条件
select id, name, status from users where name='123' or name='before' group by name having max(status) > 1;

5.转化为子查询、联合查询
以上使用方式还存在很多不足，比如
不能在得到虚拟表后、分组时，根据虚拟表中具体数据进行分组；比如取每个结果集中最大的status以及所对应的记录输出
需要利用子查询、联合查询实现

场景一：取每个name组中，最大的staus的所对应的记录

子查询：select * from (select * from users group by name, status order by status desc) u group by name;