由于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;
网友评论