每日SQL之50道SQL训练-one

SQL之50道SQL-第一题

问题

查询" 01 “课程比” 02 “课程成绩高的学生的信息及课程分数

分析

1. 涉及表：学生表 Student，成绩表 SC
2. 成绩与学生表的关联：SId
3. 筛选条件：" 01 “课程比” 02 “课程成绩高
4. 筛选条件涉及的表：成绩表 SC
5. 如何筛选：查询各"01"成绩分数和“02”课程分数，然后比较
6. 两科成绩的关联，同一个学生成绩，SId相同
7. 分步骤组合比较

分布查询

1. "01"课程的成绩

SELECT * FROM SC WHERE CId = "01"

2. "02"课程的成绩

SELECT * FROM SC WHERE CId = "02"

3. 合成"01"课程比"02"课程成绩高的表

SELECT *
FROM (SELECT *
FROM SC WHERE CId = "01" ) as a ,
(SELECT *  FROM SC WHERE CId ="02" ) AS b WHERE a.SId = b.SId AND a.score >b.score

得到结果：

因为每个表都有sid、cid、score所以出现两个sid、cid、score，可以做一些处理，将表中的数据描述的更清晰

select a.Sid ,a.score,a.CId,b.SId as SId_02,b.CId as CId_02,b.score as score_02
    from (select * from SC where Cid = '01') a
    join (select * from SC where Cid = '02') b
    on a.Sid = b.Sid
    and a.score > b.score

得到结果：

4. 联合学生表(联合的关键是成绩与学生表的关联：SId)

select 
    Student.* , r.*
from
    (select a.Sid ,a.score,a.CId,b.SId as SId_02,b.CId as CId_02,b.score as score_02
    from (select * from SC where Cid = '01') a
    join (select * from SC where Cid = '02') b
    on a.Sid = b.Sid
    and a.score > b.score) r
left join Student
on r.Sid = Student.Sid;

或者

SELECT * 
FROM Student ,(SELECT a.SId,a.score,a.CId,b.SId as SId_02,b.CId as CId_02,b.score as score_02
FROM (SELECT *
FROM SC WHERE CId = "01" ) as a ,
(SELECT *  FROM SC WHERE CId ="02" ) AS b WHERE a.SId = b.SId AND a.score >b.score) as r 
WHERE Student.SId=r.SId

得到结果