本文精选50道MySQL实战题目，涵盖数据查询、统计、排序等核心技能，通过实例解析帮助读者掌握SQL技巧，提升数据库操作能力。
小崽的MySQL 50题笔记（2020/06/24）
注意事项：
1.本文测试题目及答案参考知乎猴子，原主文章在思路点拨方面很详尽，特此分享(link:https://zhuanlan.zhihu.com/p/38354000)
2.本文MYSQL运行平台：在线模拟器http://sqlfiddle.com/
3.本文写作目的：①记录做题时疑问与问题；②补充输出结果
格式注意
1.MYSQL在语句末要加上“；”
1.数据表
[SQL Fiddle][1]
MySQL 5.6 Schema Setup:
create table Student (SId varchar(10),Sname varchar(10),Sage datetime,Ssex varchar(10));
insert into Student values('01' , 'ZHAO LI' , '1990-01-01' , 'MALE');
insert into Student values('02' , 'QIAN YI' , '1990-12-21' , 'MALE');
insert into Student values('03' , 'SUN YI' , '1990-12-20' , 'MALE');
insert into Student values('04' , 'LI YU' , '1990-12-06' , 'MALE');
insert into Student values('05' , 'ZHOU MEI' , '1991-12-01' , 'FEMALE');
insert into Student values('06' , 'WU LAN' , '1992-01-01' , 'FEMALE');
insert into Student values('07' , 'ZHENG YUN' , '1989-01-01' , 'FEMALE');
insert into Student values('09' , 'ZHANG SAN' , '2017-12-20' , 'FEMALE');
insert into Student values('10' , 'LUO YU' , '2017-12-25' , 'FEMALE');
insert into Student values('11' , 'SUN YUN' , '2012-06-06' , 'MALE');
insert into Student values('12' , 'SUN ER' , '2013-06-13' , 'MALE');
insert into Student values('13' , 'SUN YI' , '2014-06-01' , 'MALE');
create table Course (CId varchar(10),Cname nvarchar(10),TId varchar(10));
insert into Course values('01' , 'CHINESE' , '02');
insert into Course values('02' , 'MATH' , '01');
insert into Course values('03' , 'ENGLISH' , '03');
create table Teacher (TId varchar(10),Tname varchar(10));
insert into Teacher values('01' , 'ZHANG YUN');
insert into Teacher values('02' , 'MA YU');
insert into Teacher values('03' , 'QI SIYUN');
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);
Query 1:
-- 1.1查询姓“zhao”的学生名单
SELECT *
from STUDENT
WHERE SNAME LIKE 'ZHAO%'
[Results][2]:
| SId | Sname | Sage | Ssex |
|-----|---------|----------------------|------|
| 01 | ZHAO LI | 1990-01-01T00:00:00Z | MALE |
Query 2:
-- 1.2查询姓'zhao'的老师数量
Select count(TID)
from TEACHER
WHERE TNAME LIKE 'ZHAO%'
[Results][3]:
| count(TID) |
|------------|
| 0 |
Query 3:
-- 2.1查询课程2的总成绩
SELECT SUM(SCORE)
from SC
where CID = '02'
[Results][4]:
| SUM(SCORE) |
|------------|
| 436 |
Query 4:
-- 2.2查询选了课的学生人数
SELECT count( DISTINCT SID) as 'student number'
from SC
[Results][5]:
| student number |
|----------------|
| 7 |
Query 5:
-- 2.3查询各科最高成绩和最低成绩
SELECT CID,max(score),min(score)
from SC
Group by CID
[Results][6]:
| CID | max(score) | min(score) |
|-----|------------|------------|
| 01 | 80 | 31 |
| 02 | 90 | 30 |
| 03 | 99 | 20 |
Query 6:
-- 2.4查询各个科目报名的人数
SELECT CID,COUNT(DISTINCT SID)
FROM SC
GROUP BY CID
[Results][7]:
| CID | COUNT(DISTINCT SID) |
|-----|---------------------|
| 01 | 6 |
| 02 | 6 |
| 03 | 6 |
Query 7:
-- 2.5查询男生女生人数
SELECT DISTINCT SSEX,COUNT(SSEX)
FROM STUDENT
GROUP BY SSEX
[Results][8]:
| SSEX | COUNT(SSEX) |
|--------|-------------|
| FEMALE | 5 |
| MALE | 7 |
Query 8:
-- 3.1查询平均成绩大于60的学生学号和平均成绩
SELECT SID,AVG(SCORE)
FROM SC
GROUP BY SID
HAVING AVG(SCORE)>60
[Results][9]:
| SID | AVG(SCORE) |
|-----|------------|
| 01 | 89.66667 |
| 02 | 70 |
| 03 | 80 |
| 05 | 81.5 |
| 07 | 93.5 |
Query 9:
-- 3.2查询至少选修两门课程的学生
/*说明首先查询的是学生，定语至少选修两门课，分组是学生*/
SELECT SID,COUNT(CID)
FROM SC
GROUP BY SID
HAVING COUNT(CID)>=2
[Results][10]:
| SID | COUNT(CID) |
|-----|------------|
| 01 | 3 |
| 02 | 3 |
| 03 | 3 |
| 04 | 3 |
| 05 | 2 |
| 06 | 2 |
| 07 | 2 |
Query 10:
-- 3.3 查询同名同姓学生名单并统计同名人数
/*如何算同名同姓，按照姓名分组，人数>=2即可*/
SELECT SNAME ,COUNT(*)
FROM STUDENT
GROUP BY SNAME
HAVING COUNT(*)>=2
[Results][11]:
| SNAME | COUNT(*) |
|--------|----------|
| SUN YI | 2 |
Query 11:
-- 3.4 查询不及格的课程并按照课程号排列
SELECT CID,SCORE
FROM SC
WHERE SCORE<60
ORDER BY CID
[Results][12]:
| CID | SCORE |
|-----|-------|
| 01 | 50 |
| 01 | 31 |
| 02 | 30 |
| 03 | 20 |
| 03 | 34 |
Query 12:
-- 3.5 查询每门课程的平均成绩，结果按平均成绩升序排序，平均成绩相同时，按课程号降序排列
SELECT CID,AVG(SCORE)
FROM SC
GROUP BY CID
ORDER BY AVG(SCORE) ASC,CID DESC
[Results][13]:
| CID | AVG(SCORE) |
|-----|------------|
| 01 | 64.5 |
| 03 | 68.5 |
| 02 | 72.66667 |
Query 13:
-- 3.6 检索课程编号为“03”且分数小于60的学生学号，结果按按分数降序排列
SELECT SID,SCORE,CID
FROM SC
WHERE CID=03
HAVING SCORE<60
ORDER BY SCORE DESC
[Results][14]:
| SID | SCORE | CID |
|-----|-------|-----|
| 06 | 34 | 03 |
| 04 | 20 | 03 |
Query 14:
-- 3.7 统计每门课程的学生选修人数(超过2人的课程才统计)
-- 要求输出课程号和选修人数，查询结果按人数降序排序，若人数相同，按课程号升序排序
SELECT CID,COUNT(SID)
FROM SC
GROUP BY CID
ORDER BY COUNT(SID) DESC ,CID ASC
[Results][15]:
| CID | COUNT(SID) |
|-----|------------|
| 01 | 6 |
| 02 | 6 |
| 03 | 6 |
Query 15:
-- 查询两门以上不及格课程的同学的学号及其平均成绩
SELECT SID,AVG(SCORE)
FROM SC
WHERE SCORE<60
GROUP BY SID
HAVING COUNT(CID)>2
[Results][16]:
| SID | AVG(SCORE) |
|-----|------------|
| 04 | 33.33333 |
Query 16:
-- 4.1查询学生的总成绩并进行排名
SELECT SID,SUM(SCORE)
FROM SC
GROUP BY SID
ORDER BY SUM(SCORE) DESC
[Results][17]:
| SID | SUM(SCORE) |
|-----|------------|
| 01 | 269 |
| 03 | 240 |
| 02 | 210 |
| 07 | 187 |
| 05 | 163 |
| 04 | 100 |
| 06 | 65 |
Query 17:
-- 4.2查询平均成绩大于60分的学生的学号和平均成绩
SELECT SID,AVG(SCORE)
FROM SC
GROUP BY SID
HAVING AVG(SCORE)>60
[Results][18]:
| SID | AVG(SCORE) |
|-----|------------|
| 01 | 89.66667 |
| 02 | 70 |
| 03 | 80 |
| 05 | 81.5 |
| 07 | 93.5 |
Query 18:
-- 4.3查询所有课程成绩小于60分学生的学号、姓名
SELECT SID,SNAME
FROM STUDENT
WHERE SID IN (
SELECT SID
FROM SC
WHERE SCORE<60
)
[Results][19]:
| SID | SNAME |
|-----|--------|
| 04 | LI YU |
| 06 | WU LAN |
Query 19:
-- 4.4查询没有学全所有课的学生的学号、姓名|
SELECT SID,SNAME
FROM STUDENT
WHERE SID IN(
SELECT SID
FROM SC
GROUP BY SID
HAVING COUNT(CID)<(SELECT COUNT(CID) FROM SC)
)
[Results][20]:
| SID | SNAME |
|-----|-----------|
| 01 | ZHAO LI |
| 02 | QIAN YI |
| 03 | SUN YI |
| 04 | LI YU |
| 05 | ZHOU MEI |
| 06 | WU LAN |
| 07 | ZHENG YUN |
Query 20:
-- 4.5查询出只选修了两门课程的全部学生的学号和姓名
SELECT SID,SNAME
FROM STUDENT
WHERE SID IN(
SELECT SID
FROM SC
GROUP BY SID
HAVING COUNT(CID)=2
)
[Results][21]:
| SID | SNAME |
|-----|-----------|
| 05 | ZHOU MEI |
| 06 | WU LAN |
| 07 | ZHENG YUN |
Query 21:
-- 4.61990年出生的学生名单
SELECT SID,SNAME,SAGE
FROM STUDENT
WHERE SAGE LIKE '1990-%%-%%'
[Results][22]:
| SID | SNAME | SAGE |
|-----|---------|----------------------|
| 01 | ZHAO LI | 1990-01-01T00:00:00Z |
| 02 | QIAN YI | 1990-12-21T00:00:00Z |
| 03 | SUN YI | 1990-12-20T00:00:00Z |
| 04 | LI YU | 1990-12-06T00:00:00Z |
Query 22:
-- 4.7学生当前年龄（精确到月份）
SELECT SID ,TIMESTAMPDIFF(MONTH,SAGE,NOW())/12
FROM STUDENT
[Results][23]:
| SID | TIMESTAMPDIFF(MONTH,SAGE,NOW())/12 |
|-----|------------------------------------|
| 01 | 30.4167 |
| 02 | 29.5 |
| 03 | 29.5 |
| 04 | 29.5 |
| 05 | 28.5 |
| 06 | 28.4167 |
| 07 | 31.4167 |
| 09 | 2.5 |
| 10 | 2.4167 |
| 11 | 8 |
| 12 | 7 |
| 13 | 6 |
Query 23:
-- 4.8查询本月过生日的学生
SELECT *
FROM STUDENT
WHERE MONTH(SAGE)=MONTH(NOW())
[Results][24]:
| SId | Sname | Sage | Ssex |
|-----|---------|----------------------|------|
| 11 | SUN YUN | 2012-06-06T00:00:00Z | MALE |
| 12 | SUN ER | 2013-06-13T00:00:00Z | MALE |
| 13 | SUN YI | 2014-06-01T00:00:00Z | MALE |
Query 24:
-- 5.1查询所有学生的学号、姓名、选课数、总成绩
SELECT STUDENT.SID,STUDENT.SNAME,COUNT(SC.SCORE),SUM(SC.SCORE)
FROM STUDENT LEFT JOIN SC
ON STUDENT.SID=SC.SID
GROUP BY SID
[Results][25]:
| SID | SNAME | COUNT(SC.SCORE) | SUM(SC.SCORE) |
|-----|-----------|-----------------|---------------|
| 01 | ZHAO LI | 3 | 269 |
| 02 | QIAN YI | 3 | 210 |
| 03 | SUN YI | 3 | 240 |
| 04 | LI YU | 3 | 100 |
| 05 | ZHOU MEI | 2 | 163 |
| 06 | WU LAN | 2 | 65 |
| 07 | ZHENG YUN | 2 | 187 |
| 09 | ZHANG SAN | 0 | (null) |
| 10 | LUO YU | 0 | (null) |
| 11 | SUN YUN | 0 | (null) |
| 12 | SUN ER | 0 | (null) |
| 13 | SUN YI | 0 | (null) |
Query 25:
-- 5.2查询平均成绩大于85的所有学生的学号、姓名和平均成绩
SELECT SC.SID,STUDENT.SNAME,AVG(SCORE)
FROM STUDENT RIGHT JOIN SC
ON STUDENT.SID=SC.SID
GROUP BY STUDENT.SID
HAVING AVG(SCORE)>85
[Results][26]:
| SID | SNAME | AVG(SCORE) |
|-----|-----------|------------|
| 01 | ZHAO LI | 89.66667 |
| 07 | ZHENG YUN | 93.5 |
Query 26:
-- 5.3查询学生的选课情况：学号，姓名，课程号，课程名称
SELECT A.SID,A.SNAME,B.CID,C.CNAME
FROM STUDENT A INNER JOIN SC B ON A.SID=B.SID
INNER JOIN COURSE C ON B.CID=C.CID
[Results][27]:
| SID | SNAME | CID | CNAME |
|-----|-----------|-----|---------|
| 01 | ZHAO LI | 01 | CHINESE |
| 01 | ZHAO LI | 02 | MATH |
| 01 | ZHAO LI | 03 | ENGLISH |
| 02 | QIAN YI | 01 | CHINESE |
| 02 | QIAN YI | 02 | MATH |
| 02 | QIAN YI | 03 | ENGLISH |
| 03 | SUN YI | 01 | CHINESE |
| 03 | SUN YI | 02 | MATH |
| 03 | SUN YI | 03 | ENGLISH |
| 04 | LI YU | 01 | CHINESE |
| 04 | LI YU | 02 | MATH |
| 04 | LI YU | 03 | ENGLISH |
| 05 | ZHOU MEI | 01 | CHINESE |
| 05 | ZHOU MEI | 02 | MATH |
| 06 | WU LAN | 01 | CHINESE |
| 06 | WU LAN | 03 | ENGLISH |
| 07 | ZHENG YUN | 02 | MATH |
| 07 | ZHENG YUN | 03 | ENGLISH |
Query 27:
-- 5.4查询出每门课程的及格人数和不及格人数
SELECT CID,
SUM(CASE WHEN SCORE>=60 then 1
else 0 END) AS PASS,
SUM(CASE WHEN SCORE<60 then 1
else 0 END) AS FAIL
FROM SC
GROUP BY CID
[Results][28]:
| CID | PASS | FAIL |
|-----|------|------|
| 01 | 4 | 2 |
| 02 | 5 | 1 |
| 03 | 4 | 2 |
Query 28:
-- 5.5使用分段[100-85],[85-70],[70-60],[<60]来统计各科成绩，分别统计：各分数段人数，课程号和课程名称
select a.CID,b.CNAME,
sum(case when SCORE between 85 and 100
then 1 else 0 end) as '[100-85]',
sum(case when SCORE >=70 and SCORE<85
then 1 else 0 end) as '[85-70]',
sum(case when SCORE>=60 and SCORE<70
then 1 else 0 end) as '[70-60]',
sum(case when SCORE<60 then 1 else 0 end) as '[<60]'
from SC as a right join course as b
on a.CID=b.CID
group by a.CID,b.CNAME
[Results][29]:
| CID | CNAME | [100-85] | [85-70] | [70-60] | [<60] |
|-----|---------|----------|---------|---------|-------|
| 01 | CHINESE | 0 | 4 | 0 | 2 |
| 02 | MATH | 3 | 1 | 1 | 1 |
| 03 | ENGLISH | 2 | 2 | 0 | 2 |
Query 29:
-- 5.6查询课程编号为0003且课程成绩在80分以上的学生的学号和姓名|
SELECT A.SID,B.SNAME
FROM SC AS A
INNER JOIN STUDENT AS B
ON A.SID=B.SID
WHERE CID=03 AND SCORE>=80
[Results][30]:
| SID | SNAME |
|-----|-----------|
| 01 | ZHAO LI |
| 02 | QIAN YI |
| 03 | SUN YI |
| 07 | ZHENG YUN |
Query 30:
-- 6.1检索"0001"课程分数小于60，按分数降序排列的学生信息
select a.*,b.score
from student as a
inner join SC as b
on a.SID =b.SID
where b.SCORE <60 and b.CID =01
order by b.SCORE desc
[Results][31]:
| SId | Sname | Sage | Ssex | score |
|-----|--------|----------------------|--------|-------|
| 04 | LI YU | 1990-12-06T00:00:00Z | MALE | 50 |
| 06 | WU LAN | 1992-01-01T00:00:00Z | FEMALE | 31 |
Query 31:
-- 6.2查询不同老师所教不同课程平均分从高到低显示
select a.TID,
AVG(SCORE)
from TEACHER as a
inner join COURSE as b
on a.TID =B.TID
JOIN SC AS C
ON B.CID=C.CID
GROUP BY A.TID
order by AVG(SCORE) desc
[Results][32]:
| TID | AVG(SCORE) |
|-----|------------|
| 01 | 72.66667 |
| 03 | 68.5 |
| 02 | 64.5 |
Query 32:
-- 6.3查询课程名称为"数学"，且分数低于60的学生姓名和分数
SELECT A.SNAME,B.SCORE
FROM STUDENT AS A
INNER JOIN SC AS B ON A.SID=B.SID
INNER JOIN COURSE AS C ON B.CID=C.CID
WHERE C.CNAME='MATH' AND B.SCORE<60
[Results][33]:
| SNAME | SCORE |
|-------|-------|
| LI YU | 30 |
Query 33:
-- 6.4查询任何一门课程成绩在70分以上的姓名、课程名称和分数（与上题类似）
SELECT A.SNAME,C.CNAME,B.SCORE
FROM STUDENT AS A
INNER JOIN SC AS B ON A.SID=B.SID
INNER JOIN COURSE AS C ON B.CID=C.CID
WHERE B.SCORE>70
[Results][34]:
| SNAME | CNAME | SCORE |
|-----------|---------|-------|
| ZHAO LI | CHINESE | 80 |
| ZHAO LI | MATH | 90 |
| ZHAO LI | ENGLISH | 99 |
| QIAN YI | ENGLISH | 80 |
| SUN YI | CHINESE | 80 |
| SUN YI | MATH | 80 |
| SUN YI | ENGLISH | 80 |
| ZHOU MEI | CHINESE | 76 |
| ZHOU MEI | MATH | 87 |
| ZHENG YUN | MATH | 89 |
| ZHENG YUN | ENGLISH | 98 |
Query 34:
-- 6.5查询两门及其以上不及格课程的同学的学号，姓名及其平均成绩
/*难点：计算每个学号不及格的分数个数*/
SELECT A.SID,A.SNAME,AVG(B.SCORE)
FROM STUDENT AS A
INNER JOIN SC AS B
ON A.SID=B.SID
WHERE B.SCORE<60
GROUP BY B.SID
HAVING COUNT(B.SID)>=2
[Results][35]:
| SID | SNAME | AVG(B.SCORE) |
|-----|--------|--------------|
| 04 | LI YU | 33.33333 |
| 06 | WU LAN | 32.5 |
Query 35:
-- 6.5.1/*难点：计算每个学号不及格的分数个数*/
SELECT SID,COUNT(SID)
FROM SC
WHERE SCORE<60
GROUP BY SID
[Results][36]:
| SID | COUNT(SID) |
|-----|------------|
| 04 | 3 |
| 06 | 2 |
Query 36:
-- 6.6查询不同课程成绩相同的学生的学生编号、课程编号、学生成绩
/*成绩出现两次及以上*/
/*答案有问题，本式限定了SID都是相同的，该答案需要在题目中限定相同 的学生*/
SELECT distinct A.SID ,A.SCORE ,A.CID
FROM SC AS A
INNER JOIN SC AS B
ON A.SID =B.SID
WHERE A.SCORE =B.SCORE
AND A.CID != B.CID
[Results][37]:
| SID | SCORE | CID |
|-----|-------|-----|
| 03 | 80 | 02 |
| 03 | 80 | 03 |
| 03 | 80 | 01 |
Query 37:
-- 6.7查询课程编号为“0001”的课程比“0002”的课程成绩高的所有学生的学号
SELECT A.SID
FROM
(SELECT SID ,SCORE
FROM SC
WHERE CID=01) AS A
INNER JOIN
(SELECT SID ,SCORE
FROM SC
WHERE CID='02') AS B
ON A.SID=B.SID
WHERE A.SCORE>B.SCORE
[Results][38]:
| SID |
|-----|
| 02 |
| 04 |
Query 38:
-- 6.8查询学过编号为“0001”的课程并且也学过编号为“0002”的课程的学生的学号、姓名
SELECT A.SID,C.SNAME
FROM
(SELECT SID,CID
FROM SC
WHERE CID=01)AS A
INNER JOIN
(SELECT SID,CID
FROM SC
WHERE CID=02)AS B
ON A.SID=B.SID
INNER JOIN STUDENT AS C
ON A.SID=C.SID
WHERE A.SID=B.SID
[Results][39]:
| SID | SNAME |
|-----|----------|
| 01 | ZHAO LI |
| 02 | QIAN YI |
| 03 | SUN YI |
| 04 | LI YU |
| 05 | ZHOU MEI |
Query 39:
-- 6.9查询学过“MA YU”老师所教的所有课的同学的学号、姓名
SELECT D.SID,D.SNAME
FROM TEACHER AS A
INNER JOIN COURSE AS B
ON A.TID=B.TID
INNER JOIN SC AS C
ON B.CID=C.CID
INNER JOIN STUDENT AS D
ON C.SID=D.SID
WHERE A.TNAME='MA YU'
[Results][40]:
| SID | SNAME |
|-----|----------|
| 01 | ZHAO LI |
| 02 | QIAN YI |
| 03 | SUN YI |
| 04 | LI YU |
| 05 | ZHOU MEI |
| 06 | WU LAN |
Query 40:
-- 6.10查询没学过"MA YU"老师讲授的任一门课程的学生姓名（与上题类似，"没学过"用not in来实现)
SELECT SNAME
FROM STUDENT
WHERE SID NOT IN
(SELECT SID
FROM SC
WHERE CID=
(SELECT CID
FROM COURSE
WHERE TID=
(SELECT TID
FROM TEACHER
WHERE TNAME='MA YU'))
)
[Results][41]:
| SNAME |
|-----------|
| ZHENG YUN |
| ZHANG SAN |
| LUO YU |
| SUN YUN |
| SUN ER |
| SUN YI |
Query 41:
-- 6.11查询选修“MA YU”老师所授课程的学生中成绩最高的学生姓名及其成绩（与上题类似,用成绩排名，用 limit 1得出最高一个）
SELECT A.SNAME,B.SCORE
FROM STUDENT AS A
INNER JOIN SC AS B
ON A.SID=B.SID
INNER JOIN COURSE AS C
ON B.CID=C.CID
INNER JOIN TEACHER AS D
ON C.TID=D.TID
WHERE D.TNAME ='MA YU'
GROUP BY A.SNAME
ORDER BY SCORE DESC LIMIT 1
[Results][42]:
| SNAME | SCORE |
|---------|-------|
| ZHAO LI | 80 |
Query 42:
--/*第二个方法里我选用了max函数，能出结果，不知是否可以*/
SELECT A.SNAME,MAX(B.SCORE)
FROM STUDENT AS A
INNER JOIN SC AS B
ON A.SID=B.SID
INNER JOIN COURSE AS C
ON B.CID=C.CID
INNER JOIN TEACHER AS D
ON C.TID=D.TID
WHERE D.TNAME ='MA YU'
[Results][43]:
| SNAME | MAX(B.SCORE) |
|---------|--------------|
| ZHAO LI | 80 |
Query 43:
-- 6.12查询至少有一门课与学号为“0001”的学生所学课程相同的学生的学号和姓名
select SID,SNAME
from student
where SID in
(select distinct(SID) from sc where CID in
(select CID from sc where SID='01'))
and SID !='01'
[Results][44]:
| SID | SNAME |
|-----|-----------|
| 02 | QIAN YI |
| 03 | SUN YI |
| 04 | LI YU |
| 05 | ZHOU MEI |
| 06 | WU LAN |
| 07 | ZHENG YUN |
Query 44:
-- 6.13按平均成绩从高到低显示所有学生的所有课程的成绩以及平均成绩
select a.SID,avg(a.SCORE ),
max(case when b.CNAME= 'MATH' then a.SCORE else null end ) as 'MATH',
max(case when b.CNAME= 'CHINESE' then a.SCORE else null end ) as 'CHINESE',
max(case when b.CNAME= 'ENGLISH' then a.SCORE else null end ) as 'ENGLISH'
from sc as a
inner join course as b
on a.CID =b.CID
group by a.SID
ORDER BY AVG(A.SCORE)DESC
[Results][45]:
| SID | avg(a.SCORE ) | MATH | CHINESE | ENGLISH |
|-----|---------------|--------|---------|---------|
| 07 | 93.5 | 89 | (null) | 98 |
| 01 | 89.66667 | 90 | 80 | 99 |
| 05 | 81.5 | 87 | 76 | (null) |
| 03 | 80 | 80 | 80 | 80 |
| 02 | 70 | 60 | 70 | 80 |
| 04 | 33.33333 | 30 | 50 | 20 |
| 06 | 32.5 | (null) | 31 | 34 |
Query 45-49:
– 7.1窗口函数 查询学生平均成绩及其名次
– 7.2查询每门功成绩最好的前两名学生姓名
– 7.3查询所有课程的成绩第2名到第3名的学生信息及该课程成绩（与上一题相似）
– 7.4查询各科成绩前三名的记录（不考虑成绩并列情况）（与上一题相似）
由于http://sqlfiddle.com/中使用"ROW_NUMBER()"函数总报错，还未能摸索出结果，后续需要继续找答案。
扫一扫
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
