美文网首页DB-资料
mysql排名问题

mysql排名问题

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

    由于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;
    

    相关文章

      网友评论

        本文标题:mysql排名问题

        本文链接:https://www.haomeiwen.com/subject/cqkclctx.html