• mysql分组求最新一条数据(mysql5.7实现开窗)

    需求

    求按一定字段进行分组之后的每组最新或者版本最大的一条数据。
    假设：
    如书名和书编码相同时，则获取版本号最大（或创建时间最近）的一条数据；
    PS：本文就以版本号最大为例子吧。实际依葫芦画瓢就明白其他的场景了。

    预热准备

    定义测试“书”表的DDL

    CREATE TABLE `book` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `name` varchar(255) DEFAULT NULL COMMENT '书名',
  `code` varchar(64) DEFAULT NULL COMMENT '书编码',
  `create_time` datetime DEFAULT NULL COMMENT '创建时间',
  `version` int(11) DEFAULT NULL COMMENT '版本号',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8;
    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8

    PS：简简单单的数据字段，如有不严谨的地方请不要在意，这里仅仅用于说明本文需要讲解的场景哦！！！

    随便插入几条测试数据的DML

    INSERT INTO `book` VALUES ('1', '小白', '0001', '2020-04-07 21:07:44', '1');
INSERT INTO `book` VALUES ('2', '小白', '0001', '2020-04-08 21:07:59', '2');
INSERT INTO `book` VALUES ('3', '小黑', '0002', '2020-04-08 21:08:16', '1');
INSERT INTO `book` VALUES ('4', '小明', '0003', '2020-04-01 21:08:28', '2');
INSERT INTO `book` VALUES ('5', '小明', '0003', '2020-04-08 21:08:40', '3');
    • 1
    • 2
    • 3
    • 4
    • 5
    • 6

    在这里插入图片描述
    PS：瞎插几条数据，以表心意！！！！

    方案设计

    根据上面的样本数据再结合对应的需求，那么，**在理想状态下，**就是获取得到如下情况的数据：
    在这里插入图片描述
    PS：为了让各位看的清楚，就特意按照id从小到大的排序了哈！！

    错误方案一

    采取简单直接的方式，即先分组和排序一起操作

    select *
FROM 
book
GROUP BY name,code 
order by version desc, id asc
    • 1
    • 2
    • 3
    • 4
    • 5

    结果

    在这里插入图片描述
    结论：这样的方案是错误的
    对比，理想情况和实际情况，只能说，这种方案当然不行啦。那么为何不行呢？请继续看。。。。。

    分析

    1. 首先，确实是将相同的name和code的内容进行了分组，即同样的name和code的数据只存在一条，说明group by没有毛病，是分组了。
    2. order by：其中id asc是升序，那么结果确实也是升序这个没问题；假设，觉得没用，那么大家可以试试没有加id asc的情况就如下，说明id asc 是生效了。
      在这里插入图片描述
    3. order by：其中version desc是倒序，那么再看下实际情况，确实也是按照version倒序进行排序，那么说version desc也是有效果的。那么到底哪里有问题呢？

    结论：这是因为当group by 和order by同时出现的时候，它是先执行group by 分组然后才对分组的结果进行的排序。因此，执行顺序是有问题，这样自然无法实现我们需要的取每组最大版本号的数据了。

    错误方案二

    经过上面错误方案的解析，那么这一种肯定可以。。自信满满！！！

    select * 
from 
(
select *
from 
book
ORDER BY version desc
)as t1
GROUP BY t1.name,t1.code 
order by id asc
    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10

    结果

    在这里插入图片描述
    结论：这个方案结果还是不对！！！

    分析

    1. 通过实际结果观察，id确实是升序，但是每条数据的version字段却不是最大的呀。
    2. 通过第一种错误方案说到，要先根据version排序，然后再group by 分组。那看看SQL，确实是先排序，再分组了。可是为什么不行？

    结论：该方案还是由于排序和分组的效果被优化导致的。因为当外层存在group by语法时，会导致内层的order by 会失效，而mysql会默认采取“第一条”。关键来了，“第一条”，这个第一条并不是排序后的第一条，而是插入数据库同等分组条件下的顺序的第一条哦！
    假设，我们把初始的数据变成如下，然后再执行该方案的SQL就发现结果与期望的一样啦。其原因就是在于上面说的。

    =======================================

    好气呀，这两种方案都不行，那到底怎么写呢。
    别慌，继续往下面看

    =======================================

    正确方案一

    select * 
from 
(
select *
from 
book
ORDER BY version desc
limit 100000
)as t1
GROUP BY t1.name,t1.code 
order by id asc
    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11

    结果

    在这里插入图片描述
    结论：哇塞，实际和预期是一样的结果了哦！！！

    分析

    1. 我们对比一下错误方案二和现在这种方案，发现，就是因为该mysql内层采取了limit关键字进行“分页”处理。
    2. why？why？why？

    结论： 因为在内层子查询中采取limit和order by同时作用的话，就会将子查询的结果根据对应语法进行实际的先排序后分页，而不会与外层的group by语法进行优化。因此，这样的效果就可以实现获取每组的版本号最大的数据了。
    缺点： 很多朋友也应该想到了，那么就是如果子查询的结果集的条数是不知道的呢？那么limit如果小了，就会导致一些数据丢失，自然这样得到的结果就不准了。那么如何解决呢？
    优化： 既然无法确定实际的查询数据条数，那么就可以先查询一下总的数据条数，然后limit就以该结果作为参数不就可以了嘛。对的，先查询子查询的总数据条数，然后limit该结果就可以了。切记，是分开两个SQL语句了哦！！！！
    注意点： 这里要采取inner join而别采取left join或者right join（它们三者的区别就不多说了），除非你确定你的数据条件中，不存在null的情况，而都是一一对应而都存在，那么就没问题；

    正确方案二

    上面的方案存在一定的问题，那么还可以怎么做呢？

    
select t1.*
from 
book as t1
INNER JOIN
(
SELECT name,code,max(version) as version
from 
book
GROUP BY name,code
)as t2
on 
t1.code = t2.code 
and 
t1.name = t2.name
and 
t1.version = t2.version
order by id asc
    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11
    • 12
    • 13
    • 14
    • 15
    • 16
    • 17
    • 18

    结果

    在这里插入图片描述
    结论：实际和预期一样！

    分析

    该SQL实现的方式主要是用到了max函数的作用。
    （1）我们逐步的拆分来看：
    首先，

    SELECT name,code,max(version) as version
from 
book
GROUP BY name,code
    • 1
    • 2
    • 3
    • 4

    我们先根据要分组的内容进行group by，注意这时候select的字段也是要将分组的字段进行获取，其次，就是采取max函数获取我们需要的version最大（同理，如果是创建时间也是一样）。那么这样处理得到的结果是什么呢？自然，这样就可以获取到每组中版本号最大的数据信息。注意，此时并没有达到我们想要的结果。因为如果我们还要获取到版本号最大的其他的字段的信息，而此时只是获取到版本号最大的，而其他字段并非就是版本号最大对应的所有字段信息。
    （2）再通过inner join的语法作用。我们在（1）中已经获取到了每个分组条件以及需要版本号的最大的值，那么，通过inner join的等值连接，这样就可以根据“等值原理”获取到其对应的所有字段的信息了呀。那么就实现了我们的需求；
    缺点： 可以发现在最里层的子查询中这样的效率是不好的，是走的全表扫描（PS：如果存在其他的限制条件通过有索引可以实现一点优化），那么全表扫描的效率就不够好了。
    优化： 在里层的查询中，增加一定的限制条件，并且限制条件采取能够以索引处理；

    正确方案三

    那么还有其他的处理方案吗？

    select t1.*
from 
bookas t1
,
(
SELECT name,code,max(version) as version
from 
book
GROUP BY name,code
)as t2
where 
t1.code = t2.code 
and 
t1.name = t2.name
and 
t1.version = t2.version
order by id asc
    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11
    • 12
    • 13
    • 14
    • 15
    • 16
    • 17

    结果

    在这里插入图片描述
  • 相关阅读:
    静态成员 执行顺序
    排序之插入排序
    结构体字节对齐问题
    建模基础&UML
    C#中隐藏(new)和方法重载(overide)的区别
    培训记录
    C笔记
    用例
    .NET架构
    C#格式化日期
  • 原文地址：https://www.cnblogs.com/Handsome-Lan/p/14031584.html
Copyright © 2020-2023  润新知