|
Mysql查询每科成绩前二的学生
1.要求：查询每科成绩排名前二的学生信息解决方法：1.自关联查询优点：sql简单易懂缺点：当第一名的分数存在3个或3个以上时，无法查询到第一名的分数。同理第二也是。然后第三、第四就变成第一、第二思路：创建一张成绩表（cjb）的中间表（zjb），使用count（）函数统计成绩表的每个课程的每个学生分数被中间表的分数大于的次数。例如：课程编号1的最高分为95分，那么中间表中课程编号为1的所有分数中的最高分肯定为95（中间表就是成绩表），但是95分不大于95分，所以count（）的结果为0，同理，第二名的分数只低于第一名，所以count（）的结果为1。
2.排序、使用变量优点：修复第一种方法存在的缺陷。会把所有第一和第二的数据查出来。而且可以根据是是否不重复排名来实现不同的需求。重复排名：排名名次可重复，存在多个第一。不重复排名：排名名次唯一。缺点：sql较复杂。思路：首先对成绩表的数据进行课程编号升序，课程成绩降序的排序。然后创建三个变量。（@kkid，@rank，@ccj）@kkid变量用于记录课程id，当更换课程时，重置@rank变量的值为0。@rank变量用于记录当前排名数据。@ccj用于存储上一条数据分数，再决定@rank变量的值的时候，和当前数据行的分数进行比较。如果相等则排名不变（实现重复排名）。反之，不重复排名就直接排名加1即可。不需要@ccj变量。
1.要求：查询每科成绩排名前二的学生信息
首先分别建立学生表（xsb）、课程表（kcb）、成绩表（cjb）。
-- 创建学生表
DROP TABLE IF EXISTS `xsb`;
CREATE TABLE `xsb` (
`id` int(11) NOT NULL,
`sname` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci;
-- 插入学生数据
INSERT INTO `xsb` VALUES (1, '张三');
INSERT INTO `xsb` VALUES (2, '李四');
INSERT INTO `xsb` VALUES (3, '王五');
INSERT INTO `xsb` VALUES (4, '赵六');
INSERT INTO `xsb` VALUES (5, '田七');
INSERT INTO `xsb` VALUES (6, '老八');
INSERT INTO `xsb` VALUES (7, '辛九');
INSERT INTO `xsb` VALUES (8, '伍十');
-- 创建课程表
DROP TABLE IF EXISTS `kcb`;
CREATE TABLE `kcb` (
`id` int(10) NOT NULL,
`kname` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci;
-- 插入课程信息
INSERT INTO `kcb` VALUES (1, '语文');
INSERT INTO `kcb` VALUES (2, '数学');
INSERT INTO `kcb` VALUES (3, '英语');
-- 创建成绩表
DROP TABLE IF EXISTS `cjb`;
CREATE TABLE `cjb` (
`sid` int(10) NOT NULL,
`kid` int(10) NULL DEFAULT NULL,
`cj` int(10) NULL DEFAULT NULL
) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci;
-- 插入成绩信息
INSERT INTO `cjb` VALUES (1, 1, 95);
INSERT INTO `cjb` VALUES (2, 1, 88);
INSERT INTO `cjb` VALUES (3, 1, 85);
INSERT INTO `cjb` VALUES (4, 1, 89);
INSERT INTO `cjb` VALUES (5, 1, 85);
INSERT INTO `cjb` VALUES (6, 1, 84);
INSERT INTO `cjb` VALUES (7, 1, 77);
INSERT INTO `cjb` VALUES (8, 1, 80);
INSERT INTO `cjb` VALUES (1, 3, 85);
INSERT INTO `cjb` VALUES (2, 3, 90);
INSERT INTO `cjb` VALUES (3, 3, 86);
INSERT INTO `cjb` VALUES (4, 3, 83);
INSERT INTO `cjb` VALUES (5, 3, 80);
INSERT INTO `cjb` VALUES (6, 3, 75);
INSERT INTO `cjb` VALUES (7, 3, 77);
INSERT INTO `cjb` VALUES (8, 3, 80);
INSERT INTO `cjb` VALUES (1, 2, 85);
INSERT INTO `cjb` VALUES (2, 2, 88);
INSERT INTO `cjb` VALUES (3, 2, 86);
INSERT INTO `cjb` VALUES (4, 2, 81);
INSERT INTO `cjb` VALUES (5, 2, 80);
INSERT INTO `cjb` VALUES (6, 2, 75);
INSERT INTO `cjb` VALUES (7, 2, 77);
INSERT INTO `cjb` VALUES (8, 2, 80);
解决方法：
1.自关联查询
意思：表本身关联自己得到过滤条件，即where 后面的自关联查询语句才是过滤条件。
优点：sql简单易懂
缺点：当第一名的分数存在3个或3个以上时，无法查询到第一名的分数。同理第二也是。然后第三、第四就变成第一、第二
思路：创建一张成绩表（cjb）的中间表（zjb），使用count（）函数统计成绩表的每个课程的每个学生分数被中间表的分数大于的次数。例如：课程编号1的最高分为95分，那么中间表中课程编号为1的所有分数中的最高分肯定为95（中间表就是成绩表），但是95分不大于95分，所以count（）的结果为0，同理，第二名的分数只低于第一名，所以count（）的结果为1。
总结：自关联语句中，查询大于主表分数次数小于2的课程号和分数。
select cjb.sid,cjb.kid,cjb.cj from cjb
where
(select count(1) from cjb zjb
where zjb.kid = cjb.kid and cjb.cj |