mysql排名问题

作者: 数据研究僧 | 来源:发表于2019-07-17 16:49 被阅读0次

mysql排名问题
MySQL分类排名问题
mysql经典排名问题
在MySQL中实现Rank高级排名函数
在MySQL中实现Rank高级排名函数风澈vio
mysql 排名
查询获取排名
MySQL 实现排名（分组排名）
【mysql】统计排名
mysql 分数排名

由于mysql不支持rank函数，所以mysql的排名只能通过其他方式实现。
创建表并插入记录：

#创建成绩表
CREATE TABLE `sc` (
  `Id` int(11) NOT NULL AUTO_INCREMENT,
  `Sid` int(11) DEFAULT NULL,
  `Cid` int(11) DEFAULT NULL,
  `score` int(11) DEFAULT NULL,
  PRIMARY KEY (`Id`)
) ENGINE=MyISAM AUTO_INCREMENT=97 DEFAULT CHARSET=utf8;

#插入表记录
INSERT INTO `sc` VALUES ('1', '1007', '2', '71');
INSERT INTO `sc` VALUES ('2', '1006', '2', '84');
INSERT INTO `sc` VALUES ('3', '1005', '2', '56');
INSERT INTO `sc` VALUES ('4', '1004', '2', '77');
INSERT INTO `sc` VALUES ('5', '1003', '2', '67');
INSERT INTO `sc` VALUES ('6', '1002', '2', '86');
INSERT INTO `sc` VALUES ('7', '1001', '2', '69');
INSERT INTO `sc` VALUES ('8', '1007', '1', '62');
INSERT INTO `sc` VALUES ('9', '1006', '1', '93');
INSERT INTO `sc` VALUES ('10', '1005', '1', '58');
INSERT INTO `sc` VALUES ('11', '1004', '1', '78');
INSERT INTO `sc` VALUES ('12', '1003', '1', '30');
INSERT INTO `sc` VALUES ('13', '1002', '1', '80');
INSERT INTO `sc` VALUES ('14', '1001', '1', '89');
INSERT INTO `sc` VALUES ('15', '1001', '3', '82');
INSERT INTO `sc` VALUES ('16', '1002', '3', '85');
INSERT INTO `sc` VALUES ('17', '1003', '3', '32');
INSERT INTO `sc` VALUES ('18', '1004', '3', '73');
INSERT INTO `sc` VALUES ('19', '1005', '3', '54');
INSERT INTO `sc` VALUES ('20', '1006', '3', '87');
INSERT INTO `sc` VALUES ('21', '1007', '3', '77');
INSERT INTO `sc` VALUES ('22', '1008', '3', '94');
INSERT INTO `sc` VALUES ('23', '1001', '4', '39');
INSERT INTO `sc` VALUES ('24', '1002', '4', '80');
INSERT INTO `sc` VALUES ('25', '1003', '4', '82');
INSERT INTO `sc` VALUES ('26', '1004', '4', '88');
INSERT INTO `sc` VALUES ('27', '1005', '4', '38');
INSERT INTO `sc` VALUES ('28', '1006', '4', '59');
INSERT INTO `sc` VALUES ('29', '1007', '4', '42');
INSERT INTO `sc` VALUES ('30', '1008', '4', '64');
INSERT INTO `sc` VALUES ('31', '1001', '5', '89');
INSERT INTO `sc` VALUES ('32', '1002', '5', '70');
INSERT INTO `sc` VALUES ('33', '1003', '5', '60');
INSERT INTO `sc` VALUES ('34', '1004', '5', '58');
INSERT INTO `sc` VALUES ('35', '1005', '5', '38');
INSERT INTO `sc` VALUES ('36', '1006', '5', '92');
INSERT INTO `sc` VALUES ('37', '1007', '5', '73');
INSERT INTO `sc` VALUES ('38', '1008', '5', '64');
INSERT INTO `sc` VALUES ('39', '1001', '6', '49');
INSERT INTO `sc` VALUES ('40', '1002', '6', '90');
INSERT INTO `sc` VALUES ('41', '1003', '6', '70');
INSERT INTO `sc` VALUES ('42', '1004', '6', '48');
INSERT INTO `sc` VALUES ('43', '1005', '6', '58');
INSERT INTO `sc` VALUES ('44', '1006', '6', '59');
INSERT INTO `sc` VALUES ('45', '1007', '6', '72');
INSERT INTO `sc` VALUES ('46', '1008', '6', '74');
INSERT INTO `sc` VALUES ('47', '1001', '7', '49');
INSERT INTO `sc` VALUES ('48', '1002', '7', '50');
INSERT INTO `sc` VALUES ('49', '1003', '7', '70');
INSERT INTO `sc` VALUES ('50', '1004', '7', '88');
INSERT INTO `sc` VALUES ('51', '1005', '7', '48');
INSERT INTO `sc` VALUES ('52', '1006', '7', '99');
INSERT INTO `sc` VALUES ('53', '1007', '7', '82');
INSERT INTO `sc` VALUES ('93', '1009', '1', '98');
INSERT INTO `sc` VALUES ('94', '1009', '3', '78');
INSERT INTO `sc` VALUES ('95', '1009', '5', '100');
INSERT INTO `sc` VALUES ('96', '1009', '7', '87');

1、查询每门功课成绩最好的前两名

select * from sc  a where 
(select count(*) from sc b where a.cid=b.cid
and a.score<=b.score)<=2
ORDER BY a.cid;

2、查询每门功课成绩最差的前两名

select * from sc  a where 
(select count(*) from sc b where a.cid=b.cid
and a.score>=b.score)<=2
ORDER BY a.cid;

3、case when 函数的用法

select s1.cid as '课程ID', cname as '课程名称',
sum(case when s1.score BETWEEN 85 and 100 then 1 else 0 end)
as "[100-85]",
sum(case  when s1.score BETWEEN 70 and 85 then 1 else 0 end)
as "[70-85]",
sum(case   when s1.score BETWEEN 60 and 70 then 1 else 0 end)
as "[60-70]",
sum(case   when s1.score BETWEEN 0 and 60 then 1 else 0 end)
as "[0-60]"
from sc as s1 inner join course as c 
on s1.cid=c.Cid
group by s1.cid;

4、查询不同课程成绩相同的同学的学号，课程号，学生成绩——
（这个地方用到自链接的方式）

select a.sid,a.cid,a.score from sc as a 
inner join sc as b 
on a.sid =b.sid 
where a.cid<>b.cid
and a.score = b.score
ORDER BY a.score;

5、查询同名同姓学生名单，并统计同名人数
刚拿到这个题会没思路，在这里不妨换个思路试试，同名同姓即意味着sname的个数是大于1的，沿着这个思路考虑，
只要统计出来sname大于1的学生名字和个数就行了。

select sname ,count(sname) from student  
GROUP BY sname having count(sname)>1;

6、查询 001课程比002课程成绩高的所有学生的学号
这里因为要查询的是001课程中的学号，所以分布考虑：
第一步：分别查询001、002的成绩
第二部：组合最终结果从001得来的所以直接从001表中查询

select a.sid from 
(select sid,score from sc where cid =001) as a
inner JOIN
(select sid,score from sc where cid =002) as b
on a.sid=b.sid where a.score>b.score;