mysql按某一字段分组取最大(小)值所在行的数据

表结构脚本：

CREATE TABLE `student_grade` (

`id` int(11) NOT NULL AUTO_INCREMENT,

`name` varchar(64) DEFAULT NULL,

`grade` int(11) DEFAULT NULL,

`subject` varchar(64) DEFAULT NULL,

PRIMARY KEY (`id`)

) ENGINE=InnoDB AUTO_INCREMENT=10 DEFAULT CHARSET=utf8;

表数据：

--方法1：  

SELECT

s.*

FROM

student_grade s

WHERE

s.grade = (

SELECT

MAX(grade)

FROM

student_grade

WHERE

NAME = s.`name`

ORDER BY

`name`

)

--方法2：  

SELECT

s.*

FROM

student_grade s,

(

SELECT

`name`,

MAX(grade) grade

FROM

student_grade

GROUP BY

`name`

) b

WHERE

s. `name` = b.`name` AND s.grade = b.grade 

--方法

SELECT

s.*

FROM

student_grade s

INNER JOIN (

SELECT

`name`,

MAX(grade) grade

FROM

student_grade

GROUP BY

`name`

) b ON s. NAME = b. NAME

AND s.grade = b.grade