mysql下如何实现分组排序

sql脚本如下

CREATE TABLE `NewTable` (

`id` int(11) NULL DEFAULT NULL ,

`name` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL ,

`sex` bit(1) NULL DEFAULT NULL ,

`score` int(11) NULL DEFAULT NULL ,

`flag` bit(1) NULL DEFAULT NULL ,

UNIQUE INDEX `id` (`id`) USING BTREE

)

ENGINE=InnoDB

DEFAULT CHARACTER SET=utf8 COLLATE=utf8_general_ci

ROW_FORMAT=DYNAMIC ;

方式一

SELECT a.* FROM stu AS a where (SELECT COUNT(1) FROM stu where sex = a.sex AND score > a.score) <2 ORDER BY sex,score DESC;

方式二

SET @sex:=0, @rownum:=0;

SELECT

  a.*

FROM

  (SELECT id, name,sex,score,flag,

  @rownum:= (CASE WHEN @sex = sex THEN @rownum + 1 ELSE 1 END) count,

  @sex:=sex

  FROM stu ORDER BY sex, score DESC ) AS a

WHERE count < 3;