mysql分组后取每组排名第一名的那一整条数据
测试:
DROP TABLE IF EXISTS `ranktest`;
CREATE TABLE `ranktest` (
`id` int(8) NOT NULL AUTO_INCREMENT,
`name` varchar(255) DEFAULT NULL,
`list` varchar(255) DEFAULT NULL,
`score` int(3) DEFAULT NULL,
`class` int(2) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8;
插入数据:
INSERT INTO `ranktest` VALUES ('1', 'name', 'daodao,xiaohu,xiaoqin5', '92', '1');
INSERT INTO `ranktest` VALUES ('2', 'name2', 'xiaohu,daodao,xiaoqin4', '89', '2');
INSERT INTO `ranktest` VALUES ('3', 'name2', 'xiaoqin,daodao,xiaohu3', '95', '3');
INSERT INTO `ranktest` VALUES ('4', 'name3', 'xiaoqin,daodao,xiaohu2', '99', '5');
INSERT INTO `ranktest` VALUES ('5', 'name3', 'xiaoqin,daodao,xiaohu1', '99', '3');
-- 分组后取每一组最高分的一条数据
SELECT * FROM ranktest WHERE id IN(
SELECT substring_index(GROUP_CONCAT(id ORDER BY score DESC,id ASC),',',1) id
FROM ranktest
GROUP BY `name`)
说明:
GROUP_CONCAT(id ORDER BY score DESC,id ASC):
按照name分组后,获取每一组的id,按照 score DESC(score 降序拼接id),默认是通过逗号连接,如果id相同,id按照升序排序拼接,
substring_index(GROUP_CONCAT(id ORDER BY score DESC,id ASC),',',1) :
id 拼接完成后,按照 逗号 分隔后,获取第一个逗号之前的id值
相关扩展知识:
group_concat()函数:聚合函数
1、功能:将group by产生的同一个分组中的值连接起来,返回一个字符串结果。
2、语法:group_concat( [distinct] 要连接的字段 [order by 排序字段 asc/desc ] [separator '分隔符'] )
说明:通过使用distinct可以排除重复值;如果希望对结果中的值进行排序,可以使用order by子句;separator是一个字符串值,缺省为一个逗号。
substring_index(str,delim,count)
str:要处理的字符串
delim:分隔符
count:计数
例子:str=www.wikibt.com
substring_index(str,'.',1)
结果是:www
substring_index(str,'.',2)
结果是:www.wikibt
也就是说,如果count是正数,那么就是从左往右数,第N个分隔符的左边的全部内容
相反,如果是负数,那么就是从右边开始数,第N个分隔符右边的所有内容,如:
substring_index(str,'.',-2)
结果为:wikibt.com
有人会问,如果我要中间的的wikibt怎么办?
很简单的,两个方向:
从右数第二个分隔符的右边全部,再从左数的第一个分隔符的左边:
substring_index(substring_index(str,'.',-2),'.',1);
原文链接:https://blog.csdn.net/weixin_44000635/article/details/95352835