测一测自己的Sql能力之MYSQL的GROUPBY你弄懂了吗?

本文涉及的产品
RDS MySQL Serverless 基础系列,0.5-2RCU 50GB
云数据库 RDS MySQL,集群系列 2核4GB
推荐场景:
搭建个人博客
RDS MySQL Serverless 高可用系列,价值2615元额度,1个月
简介: 采用一个SQL语句,查询出:每一位客户最后一次的下单时间、订单金额、商品名称;以及每一位客户的累计订单总笔数、最大订单金额

场景描述如下:

订单表

(包含字段有:订单ID[自增]、客户ID、下单时间、订单金额、商品名称)

采用一个SQL语句,查询出:

每一位客户最后一次的下单时间、订单金额、商品名称;以及每一位客户的累计订单总笔数、最大订单金额


看起来很简单哈,有的同学就会这样去写了,如下:

SELECT
  MAX( good_price ) AS max_good_price,
  COUNT( DISTINCT order_id, order_id ) AS sum_order_num,
  order_person_id,
  MAX( create_time ) AS create_time,
  good_price,
  order_name AS good_name 
FROM
  `t_order_info` 
GROUP BY
  order_person_id

在数据很少的时候,通过这种就很容易实现了,并且每一个用户当时只有一条对应的信息,也就是order_person_id所对应的商品信息只有互不相同的一条信息。

很显然这样是不合理的,当出现多条数据的时候就会产生对应的一个错误,那就是会取到其他的数据,显示的order_person_id为3,但却不是3的其他数据字段。所以当我们去查询的时候 发现数值不对。

SELECT * FROM  `t_order_info` WHERE order_person_id = 5 ORDER BY create_time DESC;

然后继续查看是那种原因呢?


首先我们一定要明白,GROUPBY是每次只取表格的第一条数据,比如按照order_person_id进行分类,这样就会每次遇到相同的order_person_id时候,就会取第一个职,这样就出现了上面所展示的结果。


所以首先我们可以先查出来每个下单人所对应的最新下单时间以及下单人order_person_id编号,这样我们在拿着这组编号再去联查原始表INNER JOIN,将我们的时间最大值和其他相关字段查出来,同时要按照下单人ID进行分组,这样查出来的数据存在重复的订单数据,所以我们再嵌套一层GROUPBY order_person_id,这样就会按照含有重复数据集合中的默认id顺序进行分组并从该顺序中逐一取order_person_id的第一个值,然后生成新得集合。


对比两次GROUPBY查询:

SELECT
    toi.* 
  FROM
    ( SELECT order_person_id, max( create_time ) time FROM t_order_info GROUP BY order_person_id ) tmp
    INNER JOIN t_order_info toi ON tmp.order_person_id = toi.order_person_id 
    AND tmp.time = toi.create_time 
ORDER BY order_person_id DESC
根据order_person_id进行排序查询,结果如下:
1723  小卡车 3 3
1724  小卡车 4 4
1572  小卡车-AB  5 5
1708  小卡车 6 6
1717  小卡车 6 6
1709  小卡车 7 7
1718  小卡车 7 7
1710  小卡车 8 8
1719  小卡车 8 8
1702  小卡车 9 9
1711  小卡车 9 9
1720  小卡车 9 9
1703  小卡车 10  10
1712  小卡车 10  10
1721  小卡车 10  10
1722  小卡车 11  11
SELECT
    toi.* 
  FROM
    ( SELECT order_person_id, max( create_time ) time FROM t_order_info GROUP BY order_person_id ) tmp
    INNER JOIN t_order_info toi ON tmp.order_person_id = toi.order_person_id 
    AND tmp.time = toi.create_time 
默认GROUPBY 分组ID排序
1572  小卡车-AB  5 5 7
1702  小卡车 9 9 7
1703  小卡车 10  10  7
1708  小卡车 6 6 7
1709  小卡车 7 7 7
1710  小卡车 8 8 7
1711  小卡车 9 9 7
1712  小卡车 10  10  7
1717  小卡车 6 6 7
1718  小卡车 7 7 7
1719  小卡车 8 8 7
1720  小卡车 9 9 7
1721  小卡车 10  10  7
1722  小卡车 11  11  7
1723  小卡车 3 3 7
1724  小卡车 4 4 7

正式的SQL例子,采用INNERJOIN:

SELECT *
FROM (
  SELECT toi.*
  FROM (
    SELECT 
    order_person_id,
    max(create_time) TIME
    FROM t_order_info
    GROUP BY order_person_id
    ) tmp
  INNER JOIN t_order_info toi ON tmp.order_person_id = toi.order_person_id
    AND tmp.TIME = toi.create_time
  ) AS a
GROUP BY order_person_id;
--  GROUP BY 默认取第一个

第二步进行聚合函数的操作:

SELECT
  MAX( good_price ) AS max_good_price,
  COUNT( order_id ) AS sum_order_num,
  MAX( create_time ) AS max_create_time,
  order_person_id
FROM
  `t_order_info` 
GROUP BY
  order_person_id;

最终版本:

最终版:
SELECT
  MAX( toi1.good_price ) AS max_good_price,
  COUNT( toi1.order_id ) AS sum_order_num,
  toi1.order_person_id,
  max_create_time, 
  b.good_price,
  b.order_name,
  b.order_person_id
FROM
  `t_order_info`AS toi1 INNER JOIN (
  SELECT
max(create_time) AS max_create_time, good_price,order_name,order_person_id
FROM
  (
  SELECT
    toi.* 
  FROM
    ( SELECT order_person_id, max( create_time ) time FROM t_order_info GROUP BY order_person_id ) tmp
    INNER JOIN t_order_info toi ON tmp.order_person_id = toi.order_person_id 
    AND tmp.time = toi.create_time 
  ) AS a 
GROUP BY  
  order_person_id
  ) AS b ON toi1.order_person_id = b.order_person_id
  GROUP BY
  b.order_person_id

还可以根据最新的时间进行分组,不采用INNERJOIN的形式。

SELECT * FROM (
SELECT
    order_id,
    order_person_id,
    good_price,
    order_name AS good_name,
    create_time 
  FROM
    `t_order_info` 
  WHERE create_time = (
    SELECT
    MAX( create_time )) 
  GROUP BY
    order_person_id,
    (SELECT MAX( create_time )) 
  ORDER BY
    create_time DESC    
) AS a GROUP BY
    order_person_id

最终版本:

最终版:
SELECT
  MAX( toi.good_price ) AS max_good_price,
  COUNT( toi.order_id ) AS sum_order_num,
  MAX( toi.create_time ) AS max_create_time,
  toii.good_price,
  toii.good_name,
  toii.order_person_id 
FROM
  `t_order_info` AS toi
  INNER JOIN (
  SELECT
    * 
  FROM
    ( SELECT order_person_id, good_price, order_name AS good_name, create_time FROM `t_order_info` GROUP BY order_person_id, ( SELECT MAX( create_time )) ORDER BY create_time DESC ) AS a 
  GROUP BY
    order_person_id 
  ) AS toii ON toi.order_person_id = toii.order_person_id 
GROUP BY
  toii.order_person_id

以下是其他排查语句,也先列在这里。

SELECT max(create_time), order_person_id FROM t_order_info GROUP BY order_person_id;
SELECT max(create_time), good_price,order_name,order_person_id FROM t_order_info GROUP BY order_person_id;

同时这里面里一个终极版本的写法:

select 
  temp.order_person_id  客户ID,
  max(temp.create_time) 最后一次的下单时间,
  temp.good_price  最后一次的下单金额,
  temp.order_name  最后一次的下单商品,
  count(*)  客户的累计订单总笔数,
  max(good_price)  最大订单金额
from (select * from t_order_info order by create_time desc, order_id desc limit 9999999999) as temp
group by temp.order_person_id;

这是我的直属领导给我写的语句,多么的简短,美丽大方,留做纪念。


欢迎感兴趣的小伙伴一起探讨学习知识,以上是个人的一些总结分享,如有错误的地方望各位留言指出,十分感谢。

觉得有用的话别忘点赞、收藏、关注,手留余香! 😗 😗 😗

相关实践学习
如何快速连接云数据库RDS MySQL
本场景介绍如何通过阿里云数据管理服务DMS快速连接云数据库RDS MySQL,然后进行数据表的CRUD操作。
全面了解阿里云能为你做什么
阿里云在全球各地部署高效节能的绿色数据中心,利用清洁计算为万物互联的新世界提供源源不断的能源动力,目前开服的区域包括中国(华北、华东、华南、香港)、新加坡、美国(美东、美西)、欧洲、中东、澳大利亚、日本。目前阿里云的产品涵盖弹性计算、数据库、存储与CDN、分析与搜索、云通信、网络、管理与监控、应用服务、互联网中间件、移动服务、视频服务等。通过本课程,来了解阿里云能够为你的业务带来哪些帮助     相关的阿里云产品:云服务器ECS 云服务器 ECS(Elastic Compute Service)是一种弹性可伸缩的计算服务,助您降低 IT 成本,提升运维效率,使您更专注于核心业务创新。产品详情: https://www.aliyun.com/product/ecs
相关文章
|
25天前
|
SQL 关系型数据库 MySQL
MySQL进阶突击系列(07) 她气鼓鼓递来一条SQL | 怎么看执行计划、SQL怎么优化?
在日常研发工作当中,系统性能优化,从大的方面来看主要涉及基础平台优化、业务系统性能优化、数据库优化。面对数据库优化,除了DBA在集群性能、服务器调优需要投入精力,我们研发需要负责业务SQL执行优化。当业务数据量达到一定规模后,SQL执行效率可能就会出现瓶颈,影响系统业务响应。掌握如何判断SQL执行慢、以及如何分析SQL执行计划、优化SQL的技能,在工作中解决SQL性能问题显得非常关键。
|
3天前
|
SQL 存储 缓存
MySQL的架构与SQL语句执行过程
MySQL架构分为Server层和存储引擎层,具有高度灵活性和可扩展性。Server层包括连接器、查询缓存(MySQL 8.0已移除)、分析器、优化器和执行器,负责处理SQL语句;存储引擎层负责数据的存储和读取,常见引擎有InnoDB、MyISAM和Memory。SQL执行过程涉及连接、解析、优化、执行和结果返回等步骤,本文详细讲解了一条SQL语句的完整执行过程。
11 3
|
3天前
|
SQL 关系型数据库 MySQL
基于SQL Server / MySQL进行百万条数据过滤优化方案
对百万级别数据进行高效过滤查询,需要综合使用索引、查询优化、表分区、统计信息和视图等技术手段。通过合理的数据库设计和查询优化,可以显著提升查询性能,确保系统的高效稳定运行。
12 2
|
10天前
|
SQL Oracle 关系型数据库
【YashanDB 知识库】如何将 mysql 含有 group by 的 SQL 转换成崖山支持的 SQL
在崖山数据库中执行某些 SQL 语句时出现报错(YAS-04316 not a single-group group function),而这些语句在 MySQL 中能成功执行。原因是崖山遵循 SQL-92 标准,不允许选择列表中包含未在 GROUP BY 子句中指定的非聚合列,而 MySQL 默认允许这种操作。解决办法包括:使用聚合函数处理非聚合列或拆分查询为两层,先进行 GROUP BY 再排序。总结来说,SQL-92 更严格,确保数据一致性,MySQL 在 5.7 及以上版本也默认遵循此标准。
|
20天前
|
SQL 存储 关系型数据库
MySQL原理简介—10.SQL语句和执行计划
本文介绍了MySQL执行计划的相关概念及其优化方法。首先解释了什么是执行计划,它是SQL语句在查询时如何检索、筛选和排序数据的过程。接着详细描述了执行计划中常见的访问类型,如const、ref、range、index和all等,并分析了它们的性能特点。文中还探讨了多表关联查询的原理及优化策略,包括驱动表和被驱动表的选择。此外,文章讨论了全表扫描和索引的成本计算方法,以及MySQL如何通过成本估算选择最优执行计划。最后,介绍了explain命令的各个参数含义,帮助理解查询优化器的工作机制。通过这些内容,读者可以更好地理解和优化SQL查询性能。
|
2月前
|
关系型数据库 MySQL 数据库连接
数据库连接工具连接mysql提示:“Host ‘172.23.0.1‘ is not allowed to connect to this MySQL server“
docker-compose部署mysql8服务后,连接时提示不允许连接问题解决
|
24天前
|
关系型数据库 MySQL 数据库
Docker Compose V2 安装常用数据库MySQL+Mongo
以上内容涵盖了使用 Docker Compose 安装和管理 MySQL 和 MongoDB 的详细步骤,希望对您有所帮助。
131 42
|
15天前
|
关系型数据库 MySQL 网络安全
如何排查和解决PHP连接数据库MYSQL失败写锁的问题
通过本文的介绍,您可以系统地了解如何排查和解决PHP连接MySQL数据库失败及写锁问题。通过检查配置、确保服务启动、调整防火墙设置和用户权限,以及识别和解决长时间运行的事务和死锁问题,可以有效地保障应用的稳定运行。
80 25
|
3天前
|
监控 关系型数据库 MySQL
云数据库:从零到一,构建高可用MySQL集群
在互联网时代,数据成为企业核心资产,传统单机数据库难以满足高并发、高可用需求。云数据库通过弹性扩展、分布式架构等优势解决了这些问题,但也面临数据安全和性能优化挑战。本文介绍了如何从零开始构建高可用MySQL集群,涵盖选择云服务提供商、创建实例、配置高可用架构、数据备份恢复及性能优化等内容,并通过电商平台案例展示了具体应用。
|
10天前
|
SQL 关系型数据库 MySQL
数据库数据恢复——MySQL简介和数据恢复案例
MySQL数据库数据恢复环境&故障: 本地服务器,安装的windows server操作系统。 操作系统上部署MySQL单实例,引擎类型为innodb,表空间类型为独立表空间。该MySQL数据库没有备份,未开启binlog。 人为误操作,在用Delete命令删除数据时未添加where子句进行筛选导致全表数据被删除,删除后未对该表进行任何操作。