mysql分组后取每组排名第一名的那一整条数据

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`)