MySQL查询多个赛事取成绩最好成绩

数据结构

CREATE TABLE `result` (
    `id` INT ( 10 ) UNSIGNED NOT NULL AUTO_INCREMENT,
    `name` VARCHAR ( 80 ) NOT NULL DEFAULT '' COMMENT '姓名',
    `ID_number` VARCHAR ( 64 ) NOT NULL DEFAULT '' COMMENT '证件号',
    `sex` TINYINT ( 3 ) UNSIGNED NOT NULL DEFAULT '1' COMMENT '性别 1男 2女',
    `total_results` VARCHAR ( 20 ) NOT NULL DEFAULT '' COMMENT '净成绩',
    `ranking` SMALLINT ( 5 ) UNSIGNED NOT NULL DEFAULT '0' COMMENT '排名',
    `match_group_title` VARCHAR ( 20 ) NOT NULL DEFAULT '' COMMENT '组别名称',
    `match` TINYINT ( 3 ) UNSIGNED NOT NULL DEFAULT '1' COMMENT '赛事',
    PRIMARY KEY ( `id` ) USING BTREE
) ENGINE = INNODB  COMMENT = '成绩表';

部分数据

image.png

需求:查询这个人所有赛事排名最靠前的数据

解决sql

    SELECT
    result.name,result.ID_number,result.total_results,result.ranking,result.match_group_title,result.`match`
FROM
    result,
    ( SELECT result.ID_number, min( ranking ) AS `ranking` FROM  result   where `match`  BETWEEN 29 and 70 and member_type = 1 GROUP BY ID_number ) b 
WHERE
    result.ranking = b.ranking 
    AND result.ID_number = b.ID_number ORDER BY result.ranking;

image.png