经典SQL50题（21~30）
创建表SQL
create table Student(sid varchar(10),sname varchar(10),sage datetime,ssex nvarchar(10));
insert into Student values('01' , '赵雷' , '1990-01-01' , '男');
insert into Student values('02' , '钱电' , '1990-12-21' , '男');
insert into Student values('03' , '孙风' , '1990-05-20' , '男');
insert into Student values('04' , '李云' , '1990-08-06' , '男');
insert into Student values('05' , '周梅' , '1991-12-01' , '女');
insert into Student values('06' , '吴兰' , '1992-03-01' , '女');
insert into Student values('07' , '郑竹' , '1989-07-01' , '女');
insert into Student values('08' , '王菊' , '1990-01-20' , '女');
create table Course(cid varchar(10),cname varchar(10),tid varchar(10));
insert into Course values('01' , '语文' , '02');
insert into Course values('02' , '数学' , '01');
insert into Course values('03' , '英语' , '03');
create table Teacher(tid varchar(10),tname varchar(10));
insert into Teacher values('01' , '张三');
insert into Teacher values('02' , '李四');
insert into Teacher values('03' , '王五');
create table SC(sid varchar(10),cid varchar(10),score decimal(18,1));
insert into SC values('01' , '01' , 80);
insert into SC values('01' , '02' , 90);
insert into SC values('01' , '03' , 99);
insert into SC values('02' , '01' , 70);
insert into SC values('02' , '02' , 60);
insert into SC values('02' , '03' , 80);
insert into SC values('03' , '01' , 80);
insert into SC values('03' , '02' , 80);
insert into SC values('03' , '03' , 80);
insert into SC values('04' , '01' , 50);
insert into SC values('04' , '02' , 30);
insert into SC values('04' , '03' , 20);
insert into SC values('05' , '01' , 76);
insert into SC values('05' , '02' , 87);
insert into SC values('06' , '01' , 31);
insert into SC values('06' , '03' , 34);
insert into SC values('07' , '02' , 89);
insert into SC values('07' , '03' , 98);
表结构
学生表 Student
sid 学生编号 sname 学生姓名 sage 出生年月 ssex 学生性别
课程表 Course
cid 课程编号 cname 课程名称 tid 教师编号
教师表 Teacher
tid 教师编号 tname 教师姓名
成绩表 SC
sic 学生编号 cid 课程编号 score 分数
21~30题
-- 21、查询不同老师所教不同课程平均分从高到低显示
SELECT Course.tid,Course.cid,Course.cname,AVG(score) avgScore
FROM SC
LEFT JOIN Course ON SC.cid = Course.cid
GROUP BY Course.tid,SC.cid,Course.cname
ORDER BY avgScore DESC
-- 22、查询所有课程的成绩第2名到第3名的学生信息及该课程成绩
-- 这里查询的逻辑是和自身比较，大于当前记录score的记录数量count(*)+1,就是当前记录的排名
SELECT Student.sid,Student.sname,t.cid,t.score,t.rank
FROM Student
INNER JOIN(
SELECT t1.*,( SELECT count(*)+ 1 FROM SC t2 WHERE t2.cid = t1.cid AND t2.score > t1.score ) AS rank
FROM
SC t1
HAVING rank BETWEEN 2 AND 3
) t ON Student.sid=t.sid
ORDER BY cid ASC,score DESC
-- 23、统计各科成绩各分数段人数：课程编号,课程名称,[100-85],[85-70],[70-60],[0-60]及所占百分比
SELECT Course.cid,Course.cname,t.level1,t.level2,t.level3,t.level4
FROM Course
LEFT JOIN (
SELECT SC.cid,
COUNT(IF(SC.score BETWEEN 85 AND 100,1,NULL))/COUNT(*) AS level1,
COUNT(IF(SC.score BETWEEN 70 AND 85,1,NULL))/COUNT(*) AS level2,
COUNT(IF(SC.score BETWEEN 60 AND 70,1,NULL))/COUNT(*) AS level3,
COUNT(IF(SC.score BETWEEN 0 AND 60,1,NULL))/COUNT(*) AS level4
FROM SC
GROUP BY SC.cid
) t ON Course.cid=t.cid
-- 24、查询学生平均成绩及其名次
SELECT t1.*,@curRank:=@curRank+1 AS rank
FROM (
SELECT SC.sid,AVG(SC.score) AS avg
FROM SC
GROUP BY SC.sid
ORDER BY avg DESC ) t1,(SELECT @curRank:=0) t2
-- 25、查询各科成绩前三名的记录
-- 这里查询的逻辑是和自身比较，大于当前记录score的记录数量count(*)+1,就是当前记录的排名
SELECT t1.*,( SELECT count(*)+ 1 FROM SC t2 WHERE t2.cid = t1.cid AND t2.score > t1.score ) AS rank
FROM
SC t1
HAVING rank<4
ORDER BY cid ASC,score DESC
-- 26、查询每门课程被选修的学生数
SELECT SC.cid,COUNT(DISTINCT SC.sid) num
FROM SC
GROUP BY SC.cid
-- 27、查询出只选修了一门课程的全部学生的学号和姓名
SELECT Student.sid,Student.sname
FROM Student
INNER JOIN (
SELECT SC.sid
FROM SC
GROUP BY SC.sid
HAVING COUNT(SC.cid)=1) t ON Student.sid=t.sid
-- 28、查询男生、女生人数
SELECT Student.ssex,COUNT(*)
FROM Student
GROUP BY Student.ssex
-- 29、查询名字中含有"风"字的学生信息
SELECT Student.*
FROM Student
WHERE Student.sname LIKE '%风%'
-- 30、查询同名同性学生名单，并统计同名人数
SELECT Student.sname,COUNT(*) AS num
FROM Student
GROUP BY Student.sname
HAVING COUNT(*)>1