MYSQL-group by 用法解析

MYSQL-group by 用法解析

group by 用法

​ group by语法可以根据给定数据列的每个成员对查询结果进行分组统计，最终得到一个分组汇总表。
SELECT子句中的列名必须为分组列或列函数。列函数对于GROUP BY子句定义的每个组各返回一个结果。
某个历史观影进度记录表结构和数据如下：

例如，我想列出每个人的最新一条观影记录，sql语句如下：

SELECT user_id, MAX(updated_at) AS MAXIMUM
FROM movie_histories
GROUP BY user_id;

查询结果如下：

解释一下这个结果：

• 1、满足“SELECT子句中的列名必须为分组列或列函数”，因为SELECT有GROUP BY user_id中包含的列user_id。
• 2、“列函数对于GROUP BY子句定义的每个组各返回一个结果”，根据用户分组，对每个用户返回一个结果，就是每个用户的最后观影时间。
  注意：计算的是每个用户（由 GROUP BY 子句定义的组）而不是整个表的 MAX(updated_at)。

将 WHERE 子句与 GROUP BY 子句一起使用

分组查询可以在形成组和计算列函数之前使用where筛选记录。必须在GROUP BY 子句之前指定 WHERE 子句。
例如，查询用户今日每个电影的最近观影时间

select movie_id,user_id,MAX(updated_at) 
from `movie_histories`
where `created_at` > '2020-12-17 00:00:00)'
group by `movie_id` ,`user_id`;

查询结果如下：

查询结果如下：

注意：在SELECT语句中指定的每个列名也在GROUP BY子句中提到。未在这两个地方提到的列名将产生错误。
GROUP BY子句对movie_id，user_id的每个唯一组合各返回一行。

在GROUP BY子句之后使用HAVING子句

1. where条件会在分组和聚集前执行，having在分组后执行

2. where直接针对数据库字段进行筛选，having可以根据聚合函数进行筛选(也可针对字段进行过滤，但是需要先select该字段）
   例如：寻找今日数超过2个的部门的最高和最低薪水：
   例如：寻找观看电影数量超过2部的用户

select movie_id,user_id,MAX(updated_at) 
from `movie_histories`
group by `movie_id` ,`user_id`
having count(*)>2;

查询结果如下：

需求实例：

电影记录表movie_history是根据剧集来记录的用户浏览记录，以便用户挑选了多集观看时，能记录每一集的观看进度。也就是在表记录中，[user_id,movie_id,series_id]才是一组唯一键；
但是这里需要定位用户最近一次观看某部电影的进度记录；
这里的就需要根据movie_id来分组，但是分组之后，SELECT子句中的列名必须为分组列或列函数，就无法查询整条数据记录，这里的一个方法是使用where in 来实现。

select * from movie_histories
where (movie_id,updated_at)
in (
  select movie_id ,max(updated_at) 
  from `movie_histories`
  where `user_id`= 450
  group by `movie_id`
  );

对应的Eloquent查询为：

MovieHistory::whereIn(DB::raw('(movie_id,updated_at)'),function ($query)use($user) {
                    $query->select('movie_id',DB::raw('max(updated_at)'))
                        ->from('movie_histories')
                        ->where('user_id', $user->id)
                        ->groupBy('movie_id');
                }
            );