mysql使用临时量排序

前言：目前排序不单纯由高到低，由低到高。就拿成绩系统来说，列举你班级数学科目分数最高的前3，这里的前3不是前3位，如果有两个100分，一个99分，一个98分，那这里的前3是指两个100分，一个99分还是两个100分，一个99分，一个98分呢？
先给出sql：

-- ----------------------------
-- Table structure for t_student
-- ----------------------------
DROP TABLE IF EXISTS `t_student`;
CREATE TABLE `t_student` (
  `STUDENT_ID` int(10) NOT NULL,
  `CLASSES_ID` int(4) default NULL,
  `STUDENT_NAME` varchar(30) NOT NULL,
  `AGE` int(2) NOT NULL,
  PRIMARY KEY  (`STUDENT_ID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- ----------------------------
-- Records of t_student
-- ----------------------------
INSERT INTO `t_student` VALUES ('1', '1', '张三', '12');
INSERT INTO `t_student` VALUES ('2', '1', '李四', '13');
INSERT INTO `t_student` VALUES ('3', '1', '王五', '14');
INSERT INTO `t_student` VALUES ('4', '1', '酷六', '15');
INSERT INTO `t_student` VALUES ('5', '1', '小七', '13');
INSERT INTO `t_student` VALUES ('6', '1', '鸡八', '14');
INSERT INTO `t_student` VALUES ('7', '1', '傻九', '15');


-- ----------------------------
-- Table structure for t_course
-- ----------------------------
DROP TABLE IF EXISTS `t_course`;
CREATE TABLE `t_course` (
  `COURSE_ID` int(4) NOT NULL,
  `COURSE_NAME` varchar(30) NOT NULL,
  PRIMARY KEY  (`COURSE_ID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- ----------------------------
-- Records of t_course
-- ----------------------------
INSERT INTO `t_course` VALUES ('1', '语文');
INSERT INTO `t_course` VALUES ('2', '数学');
INSERT INTO `t_course` VALUES ('3', '英语');

-- ----------------------------
-- Table structure for t_classes
-- ----------------------------
DROP TABLE IF EXISTS `t_classes`;
CREATE TABLE `t_classes` (
  `CLASSES_ID` int(4) NOT NULL,
  `PID` int(4) default NULL,
  `CLASSES_NAME` varchar(30) NOT NULL,
  `LEAF` int(1) default '1',
  PRIMARY KEY  (`CLASSES_ID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- ----------------------------
-- Records of t_classes
-- ----------------------------
INSERT INTO `t_classes` VALUES ('1', '0', '班级一', '1');
INSERT INTO `t_classes` VALUES ('2', '0', '班级二', '1');
INSERT INTO `t_classes` VALUES ('3', '0', '班级三', '1');


-- ----------------------------
-- Table structure for t_grade
-- ----------------------------
DROP TABLE IF EXISTS `t_grade`;
CREATE TABLE `t_grade` (
  `STUDENT_ID` int(10) NOT NULL,
  `COURSE_ID` int(4) NOT NULL,
  `GRADE` int(10) NOT NULL default '0'
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- ----------------------------
-- Records of t_grade
-- ----------------------------
INSERT INTO `t_grade` VALUES ('1', '1', '78');
INSERT INTO `t_grade` VALUES ('1', '2', '85');
INSERT INTO `t_grade` VALUES ('1', '3', '64');
INSERT INTO `t_grade` VALUES ('2', '1', '23');
INSERT INTO `t_grade` VALUES ('2', '2', '56');
INSERT INTO `t_grade` VALUES ('2', '3', '54');
INSERT INTO `t_grade` VALUES ('3', '1', '33');
INSERT INTO `t_grade` VALUES ('3', '2', '87');
INSERT INTO `t_grade` VALUES ('3', '3', '98');
INSERT INTO `t_grade` VALUES ('4', '1', '45');
INSERT INTO `t_grade` VALUES ('4', '2', '56');
INSERT INTO `t_grade` VALUES ('4', '3', '89');
INSERT INTO `t_grade` VALUES ('5', '1', '56');
INSERT INTO `t_grade` VALUES ('5', '2', '65');
INSERT INTO `t_grade` VALUES ('5', '3', '87');
INSERT INTO `t_grade` VALUES ('6', '1', '78');
INSERT INTO `t_grade` VALUES ('6', '2', '88');
INSERT INTO `t_grade` VALUES ('6', '3', '99');
INSERT INTO `t_grade` VALUES ('7', '1', '76');
INSERT INTO `t_grade` VALUES ('7', '2', '90');
INSERT INTO `t_grade` VALUES ('7', '3', '99');

表格看得清晰吧！

/*查询最高分前三位 - 值相同，排名也相同；排名值递增不连续*/
select B.student_id, B.student_name, B.classes_name, B.total_grade, B.rank as rank from (select A.*, 
@curRank:=IF(@preRank = A.total_grade,@curRank,@incRank) AS rank,
@incRank:=@incRank + 1,
@preRank:=A.total_grade
 from (select g.student_id, s.student_name, cls.classes_name, sum(g.grade) total_grade 
        from t_grade g join t_student s on g.student_id=s.student_id 
        join t_classes cls on s.classes_id=cls.classes_id 
        group by g.student_id, s.student_name, cls.classes_name 
        order by total_grade desc )A,(SELECT @curRank:=0,@prevRank:=NULL,@incRank:=1) r
ORDER BY total_grade desc) B

/*查询最高分前三位 - 值相同，排名也相同；排名值连续递增*/
select A.*, 
CASE
WHEN @prevRank = A.total_grade THEN @curRank
WHEN @prevRank := A.total_grade THEN @curRank := @curRank + 1
END AS rank
 from (select g.student_id, s.student_name, cls.classes_name, sum(g.grade) total_grade 
        from t_grade g join t_student s on g.student_id=s.student_id 
        join t_classes cls on s.classes_id=cls.classes_id 
        group by g.student_id, s.student_name, cls.classes_name 
        order by total_grade desc )A,(SELECT @curRank:=0,@prevRank:=NULL) r

/*查询最高分前三位 - 值相同, 排名不同*/
select A.*, @curRank:=@curRank + 1 as rank
 from (select g.student_id, s.student_name, cls.classes_name, sum(g.grade) total_grade 
        from t_grade g join t_student s on g.student_id=s.student_id 
        join t_classes cls on s.classes_id=cls.classes_id 
        group by g.student_id, s.student_name, cls.classes_name 
        order by total_grade desc )A,(SELECT @curRank:=0) q

select g.student_id, s.student_name, cls.classes_name, sum(g.grade) total_grade 
        from t_grade g join t_student s on g.student_id=s.student_id 
        join t_classes cls on s.classes_id=cls.classes_id 
        group by g.student_id, s.student_name, cls.classes_name 
        order by total_grade desc 
        limit 0,3

/*查询每科最高分数*/
select s.student_id, s.student_name, cls.classes_name, c.course_name, g.grade 
        from t_grade g join t_student s on g.student_id=s.student_id 
        join t_classes cls on s.classes_id=cls.classes_id 
        join t_course c on g.course_id=c.course_id 
        where g.grade in (select max(gg.grade) from t_grade gg where gg.course_id=c.course_id)      

全CSDN的丰（好）色（se）博客，这里有书本看不到的Java技术，电影院禁播的电影，欢迎关注QQ群494808400