MYSQL-group by 用法解析

group by 用法

group by语法可以根据给定数据列的每个成员对查询结果进行分组统计,最终得到一个分组汇总表。

SELECT子句中的列名必须为分组列或列函数。列函数对于GROUP BY子句定义的每个组各返回一个结果。

某个历史观影进度记录表结构和数据如下:

by date类型 group mysql mysql里group by使用_php

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

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

查询结果如下:

by date类型 group mysql mysql里group by使用_php_02


解释一下这个结果:

  • 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`;

查询结果如下:

by date类型 group mysql mysql里group by使用_group by_03


查询结果如下:

注意:在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;

查询结果如下:

by date类型 group mysql mysql里group by使用_php_04

需求实例:

电影记录表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');
                }
            );