Mysql分组排序取每组第一条的2种实现方式

软件发布|下载排行|最新软件

当前位置:首页IT学院IT技术

Mysql分组排序取每组第一条的2种实现方式

她与月色长留   2023-03-21 我要评论

前言

记录一下最近的一个需求,查不同产品排名第一的图片作为主图

其实一开始想用的是mybatis的级联查询,结果说需要一次性全部查出来

那就没事了,改sql咯:

亲测实用

前期准备

MySQL:8.0

Java:1.8

建表语句:

CREATE TABLE `product_image` (
  `id` varchar(32) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL COMMENT '主键id',
  `product_id` varchar(32) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL COMMENT '产品id',
  `sort_id` int NOT NULL COMMENT '图片序号',
  `img_url` varchar(200) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL COMMENT '图片路径',
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci ROW_FORMAT=DYNAMIC COMMENT='产品图片表';

第一种

这一种也是网上推荐最多的,但个人觉得局限性太大,不介意用在实战上

sql:

select * from (select * from product_image order by sort_id limit 10000) a group by a.product_id

问题重点:

  • 相信个别同学复制过去执行就报了this is incompatible with sql_mode=only_full_group_by错误
  • 之所有不推荐在实战中这样写,是因为其中子查询limit 10000已经固定写死了

原因分析:

一、这个错误发生在mysql 5.7.5 版本及以上版本会出现的问题:
mysql 5.7.5版本以上默认的sql配置是:sql_mode=“ONLY_FULL_GROUP_BY”,这个配置严格执行了"SQL92标准"。
很多从5.6升级到5.7时,为了语法兼容,大部分都会选择调整sql_mode,使其保持跟5.6一致,为了尽量兼容程序。

二、在sql执行时,出现该原因,简单来说就是:
由于开启了ONLY_FULL_GROUP_BY的设置,如果select 的字段不在 group by 中,
并且select 的字段未使用聚合函数(SUM,AVG,MAX,MIN等)的话,那么这条sql查询是被mysql认为非法的,会报错误…

所以mysql在5.7.5版本及以上版本就会报这个错误

解决:

select ANY_VALUE(id),
  product_id,
 ANY_VALUE(sort_id) from (select * from product_image order by sort_id  limit 100000) a group by a.product_id

只需要把需要查询的字段用ANY_VALUE(字段)函数包住就能正常运行

有没有同学想过为什么需要limit:

看着极其多余,而且写死数量所以导致实战不实用

那是因为

从mysql5.7开始,子查询的排序已经变为无效了。所以要加个limit,这样子查询就不光是排序,所以此时排序会生效,但有条数限制10000

第二种

是使用GROUP_CONCAT函数解决的

sql:

select group_concat(distinct product_id ORDER BY sort_id), min(id) ,min(img_url)from product_image group by product_id

GROUP_CONCAT函数解释:

GROUP_CONCAT函数用于将GROUP BY产生的同一个分组中的值连接起来,返回一个字符串结果

GROUP_CONCAT函数首先根据GROUP BY指定的列进行分组,将同一组的列显示出来,并且用分隔符分隔,由函数参数(字段名)决定要返回的列

语法结构:

GROUP_CONCAT([DISTINCT] 要连接的字段 [ORDER BY 排序字段 ASC/DESC] [SEPARATOR '分隔符'])

说明:

(1) 使用DISTINCT可以排除重复值

(2) 如果需要对结果中的值进行排序,可以使用ORDER BY子句

(3) SEPARATOR '分隔符’是一个字符串值,默认为逗号

(1) 使用DISTINCT可以排除重复值

(2) 如果需要对结果中的值进行排序,可以使用ORDER BY子句

(3) SEPARATOR '分隔符’是一个字符串值,默认为逗号

其实我还是更倾向于级联查,不用再重新组装数据,代码也更简洁

总结

Copyright 2022 版权所有 软件发布 访问手机版

声明:所有软件和文章来自软件开发商或者作者 如有异议 请与本站联系 联系我们