快捷導(dǎo)航

Mysql分組排序取每組第一條的2種實現(xiàn)方式

更新時間：2023年02月15日 10:08:52 作者：她與月色長留

開發(fā)中經(jīng)常會遇到,分組查詢最新數(shù)據(jù)的問題,下面這篇文章主要給大家介紹了關(guān)于Mysql分組排序取每組第一條的2種實現(xiàn)方式,文中通過實例代碼介紹的非常詳細(xì),需要的朋友可以參考下

前言

記錄一下最近的一個需求，查不同產(chǎn)品排名第一的圖片作為主圖

其實一開始想用的是mybatis的級聯(lián)查詢，結(jié)果說需要一次性全部查出來

那就沒事了，改sql咯：

親測實用

前期準(zhǔn)備

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 '產(chǎn)品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='產(chǎn)品圖片表';

第一種

這一種也是網(wǎng)上推薦最多的，但個人覺得局限性太大，不介意用在實戰(zhàn)上

sql:

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

問題重點:

相信個別同學(xué)復(fù)制過去執(zhí)行就報了this is incompatible with sql_mode=only_full_group_by錯誤
之所有不推薦在實戰(zhàn)中這樣寫，是因為其中子查詢limit 10000已經(jīng)固定寫死了

原因分析：

一、這個錯誤發(fā)生在mysql 5.7.5 版本及以上版本會出現(xiàn)的問題：
mysql 5.7.5版本以上默認(rèn)的sql配置是:sql_mode=“ONLY_FULL_GROUP_BY”，這個配置嚴(yán)格執(zhí)行了"SQL92標(biāo)準(zhǔn)"。
很多從5.6升級到5.7時，為了語法兼容，大部分都會選擇調(diào)整sql_mode，使其保持跟5.6一致，為了盡量兼容程序。

二、在sql執(zhí)行時，出現(xiàn)該原因，簡單來說就是：
由于開啟了ONLY_FULL_GROUP_BY的設(shè)置，如果select 的字段不在 group by 中，
并且select 的字段未使用聚合函數(shù)（SUM,AVG,MAX,MIN等）的話，那么這條sql查詢是被mysql認(rèn)為非法的，會報錯誤…

所以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（字段）函數(shù)包住就能正常運行

有沒有同學(xué)想過為什么需要limit：

看著極其多余，而且寫死數(shù)量所以導(dǎo)致實戰(zhàn)不實用

那是因為：

從mysql5.7開始，子查詢的排序已經(jīng)變?yōu)闊o效了。所以要加個limit,這樣子查詢就不光是排序，所以此時排序會生效，但有條數(shù)限制10000

第二種

是使用GROUP_CONCAT函數(shù)解決的

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函數(shù)解釋:

GROUP_CONCAT函數(shù)用于將GROUP BY產(chǎn)生的同一個分組中的值連接起來，返回一個字符串結(jié)果

GROUP_CONCAT函數(shù)首先根據(jù)GROUP BY指定的列進行分組，將同一組的列顯示出來，并且用分隔符分隔，由函數(shù)參數(shù)(字段名)決定要返回的列

語法結(jié)構(gòu):
GROUP_CONCAT([DISTINCT] 要連接的字段 [ORDER BY 排序字段 ASC/DESC] [SEPARATOR '分隔符'])
說明:

(1) 使用DISTINCT可以排除重復(fù)值

(2) 如果需要對結(jié)果中的值進行排序，可以使用ORDER BY子句

(3) SEPARATOR '分隔符’是一個字符串值，默認(rèn)為逗號