由于mysql不支持rank函數(shù)摹察，所以mysql的排名只能通過其他方式實現(xiàn)豁生。
創(chuàng)建表并插入記錄：
#創(chuàng)建成績表
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 函數(shù)的用法
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咕缎、查詢同名同姓學生名單，并統(tǒng)計同名人數(shù)
剛拿到這個題會沒思路料扰，在這里不妨換個思路試試凭豪，同名同姓即意味著sname的個數(shù)是大于1的，沿著這個思路考慮晒杈，
只要統(tǒng)計出來sname大于1的學生名字和個數(shù)就行了嫂伞。
select sname ,count(sname) from student
GROUP BY sname having count(sname)>1;
6、查詢 001課程比002課程成績高的所有學生的學號
這里因為要查詢的是001課程中的學號拯钻，所以分布考慮：
第一步：分別查詢001帖努、002的成績
第二部：組合 最終結(jié)果從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;
