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:[email protected] + 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) rORDER BY total_grade desc) B

/*查询最高分前三位 - 值相同,排名也相同;排名值连续递增*/select A.*, CASEWHEN @prevRank = A.total_grade THEN @curRankWHEN @prevRank := A.total_grade THEN @curRank := @curRank + 1END 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:[email protected] + 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

分享给朋友:
您可能感兴趣的文章:
随机阅读: