Error message here!

Hide Error message here!

Error message here!

Hide Error message here!

Error message here!

Close

## sql 经典查询50题 思路（一）

https://zhuanlan.zhihu.com/p/72223558

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

select Student.SId,Student.Sname,Student.Sage,Student.Ssex,r.科目一成绩,r.科目二成绩
from study.dbo.Student
right join
(select t1.SId as 学生ID,t1.score as 科目一成绩,t2.score as 科目二成绩
from
(select SId,score from study.dbo.SC where CId='')as t1,
(select SId,score from study.dbo.SC where CId='')as t2
where t1.SId=t2.SId and t1.score>t2.score) as r
on Student.SId=r.学生ID

join -- on这个也是常用的思路，当要连接两个某一列相关的表时。

1.1查询存在" 01 "课程但可能不存在" 02 "课程的情况(不存在时显示为 null )

select *
from
(select SId,score from study.dbo.SC where CId='') as t1
left join
(select SId,score from study.dbo.SC where CId='') as t2
on t1.SId=t2.SId

1.2 查询同时存在01和02课程的情况

select t1.SId,t1.score,t2.score
from
(select SId,score from study.dbo.SC where CId='') as t1
inner join
(select SId,score from study.dbo.SC where CId='') as t2
on t1.SId=t2.SId

1.3查询选择了02课程但没有01课程的情况

select t2.SId,t2.score
from
(select SId,score from study.dbo.SC where CId='') as t1
right join
(select SId,score from study.dbo.SC where CId='') as t2
on t1.SId=t2.SId
where t1.score is null

2.查询平均成绩大于等于 60 分的同学的学生编号和学生姓名和平均成绩

select t1.SId,t1.avg_score,t2.Sname
from
(
select SId,AVG(score) as avg_score
from study.dbo.SC
group by SId
having AVG(score)>60
) as t1
inner join study.dbo.Student as t2
on t1.SId=t2.SId

3.查询在 SC 表存在成绩的学生信息

select DISTINCT Student.SId,Student.Sname,Student.Sage,Student.Ssex
from study.dbo.SC
inner join Student
on SC.SId=Student.SId

4.查询所有同学的学生编号、学生姓名、选课总数、所有课程的总成绩(没成绩的显示为 null )

select Student.*,t2.count_id,t2.avg_score
from
Student
left join
(select SId,count(CId) as count_id ,avg(score)as avg_score from study.dbo.SC group by SId) as t2
on Student.SId=t2.SId

4.1 查有成绩的学生信息

inner join，不赘述

select Student.*,t2.count_id,t2.avg_score
from
Student
inner join
(select SId,count(CId) as count_id ,avg(score)as avg_score from study.dbo.SC group by SId) as t2
on Student.SId=t2.SId

5.查询「李」姓老师的数量

select COUNT(*)
from Teacher
where Tname like '李%'

6.查询学过「张三」老师授课的同学的信息

select Student.*
from
(select tid from Teacher where Tname='张三') as t1
inner join Course on t1.tid=Course.TId
inner join SC on Course.CId=SC.CId
inner join Student on SC.SId=Student.SId

select study.dbo.Student.*
from teacher,study.dbo.Course ,study.dbo.student,study.dbo.sc
where teacher.Tname='张三' and teacher.TId=Course.TId and Course.CId=sc.CId and sc.SId=student.SId

7.查询没有学全所有课程的同学的信息

SELECT *
FROM study.dbo.Student as t1
inner join
(select Student.SId from Student
left join
study.dbo.SC
on Student.SId=SC.SId
group by Student.SId
having COUNT(SC.CId)!=(select count(*) from study.dbo.Course)) as t2
on t1.SId=t2.SId

https://www.cnblogs.com/take-it-easy/p/11775991.html