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`)
SET FOREIGN_KEY_CHECKS=0;
-- ----------------------------
-- Table structure for ranktest
-- ----------------------------
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;
-- ----------------------------
-- Records of ranktest
-- ----------------------------
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`)