参考https://blog.csdn.net/junzi528/article/details/84404412
建表与数据sql
/*
Navicat MySQL Data Transfer
Source Server : 127.0.0.1
Source Server Version : 50553
Source Host : localhost:3306
Source Database : test1
Target Server Type : MYSQL
Target Server Version : 50553
File Encoding : 65001
Date: 2019-12-27 11:30:57
*/
SET FOREIGN_KEY_CHECKS=0;
-- ----------------------------
-- Table structure for studentgrade
-- ----------------------------
DROP TABLE IF EXISTS `studentgrade`;
CREATE TABLE `studentgrade` (
`stuId` varchar(22) DEFAULT NULL,
`subId` int(11) DEFAULT NULL,
`grade` int(11) DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=utf8;
-- ----------------------------
-- Records of studentgrade
-- ----------------------------
INSERT INTO `studentgrade` VALUES ('001', '1', '99');
INSERT INTO `studentgrade` VALUES ('003', '1', '12');
INSERT INTO `studentgrade` VALUES ('002', '1', '16');
INSERT INTO `studentgrade` VALUES ('004', '1', '56');
INSERT INTO `studentgrade` VALUES ('003', '2', '99');
INSERT INTO `studentgrade` VALUES ('002', '2', '0');
INSERT INTO `studentgrade` VALUES ('004', '2', '11');
INSERT INTO `studentgrade` VALUES ('001', '2', '33');
INSERT INTO `studentgrade` VALUES ('004', '3', '22');
INSERT INTO `studentgrade` VALUES ('003', '3', '67');
INSERT INTO `studentgrade` VALUES ('001', '3', '88');
INSERT INTO `studentgrade` VALUES ('002', '3', '88');
数据表展示
语句sql
select * from studentgrade a where (select count(1) from studentgrade b where
b.subId=a.subId and b.grade>=a.grade)<=2
order by a.subid,a.grade desc
思路
核心思路:要算出某人成绩在第几名,可以转换成:算出他一共比多少人成绩高。比如,第一名的人,就没其它
人成绩比他更好。第三名的人,就有两个人成绩比他好。
where语句可以理解为,把表中的每一行记录,都去与给定的where条件作对比,满足的再查出来。也就是有
个遍历的过程。
分析过程