Mysql：查询每科成绩前二的学生信息和课程信息（即使存在多个第一，也可以有效查询）

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<zjb.cj) < 2 
 ORDER BY cjb.kid,cjb.cj DESC;

执行结果如下：

可以看到，已经查询到了每科前二的学生id，这时候再关联学生表（xsb）和课程表（kcb）即可。

select cjb.sid,xsb.sname,cjb.kid,kcb.kname,cjb.cj from cjb 
 INNER JOIN xsb on xsb.id = cjb.sid 
 INNER JOIN kcb on kcb.id = cjb.kid 
 where (select count(1) from cjb zjb where zjb.kid = cjb.kid and cjb.cj<zjb.cj) < 2 
 ORDER BY cjb.kid,cjb.cj DESC;

但是存在一个大问题！当我们修改课程编号为1，同学id为2和3的分数为95时，再执行语句就会发现，这时候查出来了三个95的成绩，原本89分的同学被过滤了，原因是大于89分的数据有3个，所以当存在三个第一时，第二名的count（）值为3，但是我们是取小于2的，所以就查询不到第二名。

-- 修改学生编号2和3的课程id为1的成绩为95
update cjb set cjb.cj = 95 where cjb.kid = 1 and cjb.sid in (2,3);

执行结果如下：

这时候就得用到第二种方法了。

2.排序、使用变量

优点：修复第一种方法存在的缺陷。会把所有第一和第二的数据查出来。而且可以根据是是否不重复排名来实现不同的需求。重复排名：排名名次可重复，存在多个第一。不重复排名：排名名次唯一。

缺点：sql较复杂。

思路：首先对成绩表的数据进行课程编号升序，课程成绩降序的排序。然后创建三个变量。（@kkid，@rank，@ccj）@kkid变量用于记录课程id，当更换课程时，重置@rank变量的值为0。@rank变量用于记录当前排名数据。@ccj用于存储上一条数据分数，再决定@rank变量的值的时候，和当前数据行的分数进行比较。如果相等则排名不变（实现重复排名）。反之，不重复排名就直接排名加1即可。不需要@ccj变量。

补充：后续发现重复排名有纰漏，如果课程2的最高分数刚刚好等于课程1的最低分数，会出现课程二最高分排名为0。优化：在决定排名名次的时候增加多一个判断。IF(@rank=0,1,@rank)

-- 重复排名：存在多个同名次
SELECT zjb.sid,xsb.sname,zjb.kid,kcb.kname,zjb.cj,zjb.lv FROM 
 (SELECT cjb.*,@rank:=IF(@kkid!=cjb.kid,0,@rank),@rank:=IF(@ccj=cjb.cj,IF(@rank=0,1,@rank),@rank+1) as lv,@ccj:=cjb.cj,@kkid:=cjb.kid FROM cjb,(SELECT @kkid:=-1,@rank:=0,@ccj:=-1) init 
 ORDER BY cjb.kid,cjb.cj DESC) zjb 
 INNER JOIN xsb on xsb.id = zjb.sid 
 INNER JOIN kcb on kcb.id = zjb.kid 
 WHERE zjb.lv in (1,2) ORDER BY zjb.kid,zjb.cj DESC,zjb.lv;

执行结果如下：

-- 不重复排名：同分数，名次不重复
SELECT zjb.sid,xsb.sname,zjb.kid,kcb.kname,zjb.cj,zjb.lv FROM 
 (SELECT cjb.*,@rank:=IF(@kkid!=cjb.kid,0,@rank),@rank:=@rank+1 as lv,@kkid:=cjb.kid FROM cjb,(SELECT @kkid:=-1,@rank:=0) init 
 ORDER BY cjb.kid,cjb.cj DESC) zjb 
 INNER JOIN xsb on xsb.id = zjb.sid 
 INNER JOIN kcb on kcb.id = zjb.kid 
 WHERE zjb.lv in (1,2) ORDER BY zjb.kid,zjb.cj DESC,zjb.lv;

执行结果如下：