初始化表student表
CREATE TABLE `student` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(32) DEFAULT NULL,
`sex` varchar(32) DEFAULT NULL,
`age` int(11) DEFAULT NULL,
`department` varchar(128) DEFAULT NULL,
`subject` varchar(32) DEFAULT NULL,
`score` double(5,2) DEFAULT NULL,
`grade` varchar(16) DEFAULT NULL COMMENT '年级',
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=70 DEFAULT CHARSET=utf8;
-- ----------------------------
-- Records of student
-- ----------------------------
INSERT INTO `student` VALUES ('1', '李四', ' 女', '20', '132', '语文', '12.97', '一年级');
INSERT INTO `student` VALUES ('3', '张三', '男', '32', null, '语文', '93.23', '一年级');
INSERT INTO `student` VALUES ('4', '王五', '女', '33', null, '语文', '93.23', '一年级');
INSERT INTO `student` VALUES ('5', '小明', '男', '23', null, '语文', '56.58', '一年级');
INSERT INTO `student` VALUES ('6', '小爱', '女', '34', null, '语文', '1.14', '一年级');
INSERT INTO `student` VALUES ('7', '晓平', '女', '54', null, '语文', '35.98', '一年级');
INSERT INTO `student` VALUES ('8', '李果', '男', '45', null, '语文', '76.45', '一年级');
INSERT INTO `student` VALUES ('9', '孙群', '男', '54', null, '语文', '74.31', '一年级');
INSERT INTO `student` VALUES ('10', '孙明', '女', '34', null, '语文', '42.21', '一年级');
INSERT INTO `student` VALUES ('11', '利好', '男', '23', null, '语文', '88.13', '一年级');
INSERT INTO `student` VALUES ('12', '离校', '女', '43', null, '语文', '14.01', '一年级');
INSERT INTO `student` VALUES ('13', '倪敏', '女', '53', null, '语文', '5.66', '一年级');
INSERT INTO `student` VALUES ('14', '尼玛', '男', '23', null, '语文', '86.26', '一年级');
INSERT INTO `student` VALUES ('15', '萨迪', '男', '45', null, '语文', '14.31', '一年级');
INSERT INTO `student` VALUES ('16', '奥巴马', '男', '34', null, '语文', '12.77', '一年级');
INSERT INTO `student` VALUES ('17', '贾跃亭', '男', '23', null, '语文', '20.91', '一年级');
INSERT INTO `student` VALUES ('18', '川普', '男', '53', null, '语文', '66.27', '一年级');
INSERT INTO `student` VALUES ('19', '李四', '女', '20', '132', '数学', '68.62', '一年级');
INSERT INTO `student` VALUES ('20', '张三', '男', '32', '', '数学', '44.29', '一年级');
INSERT INTO `student` VALUES ('21', '王五', '女', '33', '', '数学', '15.57', '一年级');
INSERT INTO `student` VALUES ('22', '小明', '男', '23', '', '数学', '45.00', '一年级');
INSERT INTO `student` VALUES ('23', '小爱', '女', '34', '', '数学', '78.29', '一年级');
INSERT INTO `student` VALUES ('24', '晓平', '女', '54', '', '数学', '56.46', '一年级');
INSERT INTO `student` VALUES ('25', '李果', '男', '45', '', '数学', '47.41', '一年级');
INSERT INTO `student` VALUES ('26', '孙群', '男', '54', '', '数学', '67.66', '一年级');
INSERT INTO `student` VALUES ('27', '孙明', '女', '34', '', '数学', '96.08', '一年级');
INSERT INTO `student` VALUES ('28', '利好', '男', '23', '', '数学', '77.42', '一年级');
INSERT INTO `student` VALUES ('29', '离校', '女', '43', '', '数学', '98.85', '一年级');
INSERT INTO `student` VALUES ('30', '倪敏', '女', '53', '', '数学', '62.00', '一年级');
INSERT INTO `student` VALUES ('31', '尼玛', '男', '23', '', '数学', '13.43', '一年级');
INSERT INTO `student` VALUES ('32', '萨迪', '男', '45', '', '数学', '81.17', '一年级');
INSERT INTO `student` VALUES ('33', '奥巴马', '男', '34', '', '数学', '65.55', '一年级');
INSERT INTO `student` VALUES ('34', '贾跃亭', '男', '23', '', '数学', '84.22', '一年级');
INSERT INTO `student` VALUES ('35', '川普', '男', '53', '', '数学', '24.48', '一年级');
INSERT INTO `student` VALUES ('36', '李四', '女', '20', '132', '英语', '69.71', '一年级');
INSERT INTO `student` VALUES ('37', '张三', '男', '32', '', '英语', '75.12', '一年级');
INSERT INTO `student` VALUES ('38', '王五', '女', '33', '', '英语', '66.48', '一年级');
INSERT INTO `student` VALUES ('39', '小明', '男', '23', '', '英语', '7.04', '一年级');
INSERT INTO `student` VALUES ('40', '小爱', '女', '34', '', '英语', '35.75', '一年级');
INSERT INTO `student` VALUES ('41', '晓平', '女', '54', '', '英语', '57.65', '一年级');
INSERT INTO `student` VALUES ('42', '李果', '男', '45', '', '英语', '81.00', '一年级');
INSERT INTO `student` VALUES ('43', '孙群', '男', '54', '', '英语', '32.04', '一年级');
INSERT INTO `student` VALUES ('44', '孙明', '女', '34', '', '英语', '17.19', '一年级');
INSERT INTO `student` VALUES ('45', '利好', '男', '23', '', '英语', '89.85', '一年级');
INSERT INTO `student` VALUES ('46', '离校', '女', '43', '', '英语', '97.69', '一年级');
INSERT INTO `student` VALUES ('47', '倪敏', '女', '53', '', '英语', '18.87', '一年级');
INSERT INTO `student` VALUES ('48', '尼玛', '男', '23', '', '英语', '1.27', '一年级');
INSERT INTO `student` VALUES ('49', '萨迪', '男', '45', '', '英语', '49.77', '一年级');
INSERT INTO `student` VALUES ('50', '奥巴马', '男', '34', '', '英语', '45.05', '一年级');
INSERT INTO `student` VALUES ('51', '贾跃亭', '男', '23', '', '英语', '75.93', '一年级');
INSERT INTO `student` VALUES ('52', '川普', '男', '53', '', '英语', '44.50', '一年级');
INSERT INTO `student` VALUES ('53', '李四', '女', '20', '132', '物理', '94.70', '一年级');
INSERT INTO `student` VALUES ('54', '张三', '男', '32', '', '物理', '39.99', '一年级');
INSERT INTO `student` VALUES ('55', '王五', '女', '33', '', '物理', '15.88', '一年级');
INSERT INTO `student` VALUES ('56', '小明', '男', '23', '', '物理', '59.42', '一年级');
INSERT INTO `student` VALUES ('57', '小爱', '女', '34', '', '物理', '49.45', '一年级');
INSERT INTO `student` VALUES ('58', '晓平', '女', '54', '', '物理', '68.97', '一年级');
INSERT INTO `student` VALUES ('59', '李果', '男', '45', '', '物理', '96.54', '一年级');
INSERT INTO `student` VALUES ('60', '孙群', '男', '54', '', '物理', '75.76', '一年级');
INSERT INTO `student` VALUES ('61', '孙明', '女', '34', '', '物理', '89.18', '一年级');
INSERT INTO `student` VALUES ('62', '利好', '男', '23', '', '物理', '18.62', '一年级');
INSERT INTO `student` VALUES ('63', '离校', '女', '43', '', '物理', '25.56', '一年级');
INSERT INTO `student` VALUES ('64', '倪敏', '女', '53', '', '物理', '71.92', '一年级');
INSERT INTO `student` VALUES ('65', '尼玛', '男', '23', '', '物理', '82.96', '一年级');
INSERT INTO `student` VALUES ('66', '萨迪', '男', '45', '', '物理', '99.01', '一年级');
INSERT INTO `student` VALUES ('67', '奥巴马', '男', '34', '', '物理', '46.17', '一年级');
INSERT INTO `student` VALUES ('68', '贾跃亭', '男', '23', '', '物理', '33.84', '一年级');
INSERT INTO `student` VALUES ('69', '川普', '男', '53', '', '物理', '30.70', '一年级');查询学生总成绩前三名（包含并列排名）
SELECT t.name,t.grade,t.sumScore,t.num as rank from (
SELECT
a.*,
CASE
WHEN a.sumScore !=@sumScore THEN
@num :=@num + 1
ELSE
@num
END AS num,
@sumScore := sumScore
FROM
(
SELECT
s.*, SUM(score) AS sumScore
FROM
student s
WHERE
s.grade = '一年级'
GROUP BY NAME
) a,
(
SELECT
@sumScore := 0,
@num := 0
FROM DUAL
) b
ORDER BY
a.sumScore DESC
) t where t.num<=3;查询各个科目的前三名学生数据（包含并列排名）
SELECT
t.name,t.age,t.sex,t.subject,t.grade,t.score,t.num as rank
FROM
(
SELECT
a.*, CASE
WHEN a.`subject`=@sub and a.score !=@score THEN
@num := @num + 1
ELSE
@num := 1
END AS num,
@score := a.score,
@sub := a.`subject`
FROM
student a,
(
SELECT
@sub := '',
@score := 0,
@num := 0
FROM
DUAL
) b
where grade = '一年级'
ORDER BY
a.`subject`,a.score desc
) t
WHERE
t.num <= 3;
版权声明：本文为qq_31127787原创文章，遵循CC 4.0 BY-SA版权协议，转载请附上原文出处链接和本声明。