MySQL8.0子查询中使用order by不生效的问题

╰半橙微兮° 2023-02-13 03:39 10阅读 0赞

### MySQL8.0子查询中使用order by不生效的问题 ###

> 0、备忘记录，在以后项目中遇到此类问题可以快速查阅

> 1、项目背景如下，获取当天每个人员门禁通行记录的最新一条记录作为出勤记录。

> 2、根据需求，想到的肯定是使用sql中的GROUP BY的分组来做。  
> 现有简化的原始数据如下，由于id采用自增策略，我们筛选出每个人员在规定时间段内(当天)记录的id最大的那条记录就可以了  
> ![在这里插入图片描述][20200527093133575.jpg]

> 3、直接进行分组操作，取到的结果是每个分组id最小的一条。

SELECT
    	t.id,
    	t.`name`,
    	t.idCard 
    FROM
    	campus_record t
    GROUP BY t.IdCard

![在这里插入图片描述][20200527093806763.jpg]

> 4、通过对以上结果进行总结，思考如下。数据自然排序下id是由小到大，在分组中我们获取到的数据id是组内最小的，如果我们先对数据进行倒序排序，分组中是否能获取到最大id的记录呢，实验如下  
> 通过实际操作，结果没有达到预期结果。郁闷中…

SELECT
    	tt.* 
    FROM
    	( SELECT t.id, t.`name`, t.idCard FROM campus_record t ORDER BY t.id DESC) AS tt 
    GROUP BY tt.idCard

![在这里插入图片描述][20200527094355422.jpg]

> 5、通过一系列查询，大概的原因是mysql自5.7进行了优化。子查询的ORDER BY竟然在实际执行中被优化掉了。怎么才能不被优化掉呢？加LIMIT是方案之一。  
> 在子句中加上LIMIT之后，我们可以看到数据的整体排序是倒序的，在每个分组中也得到了预期的数据。但是LIMIT后的值该取多大呢，又是一个问题。  
> 若被查询的数据在可控可知的范围内，我认为此方案是可行的，放上1000、10000、100000也未尝不可。若数据量完全不可控、不可知，那这种方案还是不完美

SELECT
    	tt.* 
    FROM
    	( SELECT t.id, t.`name`, t.idCard FROM campus_record t ORDER BY t.id DESC LIMIT 1000) AS tt 
    GROUP BY
    	tt.idCard

![在这里插入图片描述][20200527094824721.jpg]

> 6、经过以上思考，需要找到一个比较完美解决方案。  
> 先在每个分组中使用聚合函数MAX找到最大值的id，然后再进行联结查询应该也可以。  
> 经过这样一番操作，得到了预期的结果。这种方案不受数据多少的限制。算是完美的方案了吧!!!

SELECT
    	t2.id,
    	t2.name,
    	t2.IdCard 
    FROM
    	( 
    		SELECT MAX( t.id ) AS "id" FROM campus_record t GROUP BY t.idCard 
    	) AS t1 INNER JOIN campus_record t2 
    ON t1.id = t2.id

![在这里插入图片描述][20200527095938184.jpg]

> 小尾巴~~  
> 只要有积累，就会有进步

[20200527093133575.jpg]: https://img-blog.csdnimg.cn/20200527093133575.jpg
[20200527093806763.jpg]: https://img-blog.csdnimg.cn/20200527093806763.jpg
[20200527094355422.jpg]: https://img-blog.csdnimg.cn/20200527094355422.jpg
[20200527094824721.jpg]: https://img-blog.csdnimg.cn/20200527094824721.jpg
[20200527095938184.jpg]: https://img-blog.csdnimg.cn/20200527095938184.jpg