用了一些不同的写法，有的存在bug，在注释中标注了。
很多写法都是重复的，只是为了练手，看哪种写法更便于阅读和提高查询效率
--查询" 01 “课程比” 02 "课程成绩高的学生的信息及课程分数
---------------------------------------------------------with子查询
with
A as (select SId,score from SC where CId='01'),
B as (select SId,score from SC where CId='02')
select *
from Student
where SId in (
select A.SId from A,B
where A.score > B.score
and A.SId =B.SId
)
------------------------------------------------------------直接查询（笛卡尔积）
select * from Student where SId in
(select B.SId from
(select SId,score from SC where CId='01')A,
(select SId,score from SC where CId='02')B
where A.SId =B.SId and A.score>B.score
)
-------------------------------------------------------------inner join查询
select * from Student where SId in(
select S1.SId from
(select SId,score from SC where CId='01')S1
inner join
(select SId,score from SC where CId='02')S2
on S1.SId =S2.SId
where S1.score >S2.score )
-------------------------------------------------
select Student.Sname,c.score01,score02
from Student INNER JOIN
(SELECT a.Sid,a.score score01,b.score score02
FROM (SELECT Sid,score FROM sc WHERE cid='01') a ,
(SELECT sid,score FROM SC where cid='02') b
WHERE a.Sid=b.SId AND a.score>b.score) c
ON Student.sid=c.Sid
GO
--查询同时存在"01"课程和"02"课程的情况
-------------------------------------------------------------inner join查询
select * from Student where SId in(
select S1.SId from
(select SId from SC where CId='01')S1
inner join
(select SId from SC where CId='02')S2
on S1.SId =S2.SId
)
---------------------------------------------------------with as子查询
with
A as (select SId from SC where CId='01'),
B as (select SId from SC where CId='02')
select SId from Student where SId in
( select A.SId from A,B where A.SId=B.SId
)
--------------------------------------------------------直接查询，就是笛卡尔积查询
select * from Student where SId in
(select S1.SId from
(select SId from SC where CId='01')S1,
(select SId from SC where CId='02')S2
where S1.SId = S2.SId)
go
--查询存在"01"课程但可能不存在"02"课程的情况（不存在时显示为null)
--------------------------------------------------------------left join,只查询学号和成绩
select S1.SId 学号,
S1.score 课程01,
S2.score 课程02
from
(select SId,score from SC where CId='01')S1
left join
(select SId,score from SC where CId='02')S2
on S1.SId =s2.SId
------------------------------------------------------------多个join连用，按先后顺序连接
select S.SId 学号,
Sname 姓名,
Ssex 性别,
S1.score 成绩1,
S2.score 成绩2
from Student S
right join
(select SId,score from SC where CId='01')S1 on S.SId = s1.SId
left join
(select SId,score from SC where CId='02')S2 on S.SId = s2.SId
--------------------------------------------------------------------错误写法！！！直接查询，类似于inner join
select S.SId 学号,
S.Sname 姓名,
S1.score 成绩01,
S2.score 成绩02
from Student S,
(select SId,score from SC where CId='01')S1,
(select SId,score from SC where CId='02')S2
where S.SId = S1.SId
and S.SId = S2.SId
go
--查询不存在"01"课程但存在"02"课程的情况
-------------------------------------------------------------------用in进行查询
select * from Student
where SId not in
(select SId from SC where CId='01')
and SId in
(select SId from SC where CId='02')
-------------------------------------------------------------------用except查询
with
A as (select SId from SC where CId='01'),
B as (select SId from SC where CId='02')
select * from Student
where SId in
(
select SId from B
except
select SId from A
)
--------------------------------------------------------------------先join再where ,使用all
with
A as (select SId from SC where CId='01'),
B as (select SId from SC where CId='02')
select *
from Student S
right join B on B.SId = S.SId
where S.SId <> all(select * from A)
go
--查询平均成绩大于等于 60 分的同学的学生编号和学生姓名和平均成绩
--------------------------------------------------------------------------------用with as
with
A as (select SId,AVG(score) 平均值 from SC group by SId having AVG(score)>=60)
select S.SId 学生编号,
S.Sname 学生姓名,
A.平均值
from Student S,A
where S.SId =A.SId
----------------------------------------------------------------------------------不用with
select S.SId 学生编号,
S.Sname 学生姓名,
A.平均值
from Student S,
(select SId,AVG(score) 平均值 from SC group by SId having AVG(score)>=60)A
where S.SId =A.SId
----------------------------------------------------------------------------------left join
select *
from
(select s.Sid,s.Sname ,avg(score) avgscore
from Student S left join SC S2 on S.Sid = S2.SId
group by s.Sid, s.Sname) a ---SId相同的，Sname也相同，但是这样写，可以让两者都能在select的结果中显示
where a.avgscore>60
go
--查询在 SC 表存在成绩的学生信息
--------------------------------------------------------------------------------- in
select * from Student
where SId in
(select distinct SId from SC)
---------------------------------------------------------------------------------any
select * from Student
where SId =
any(select SId from SC)
go
--查询所有同学的学生编号、学生姓名、选课总数、所有课程的总成绩(没成绩的显示为 null )
-------------------------------------------------------------------------------------------left join + with + group by
with
A as (select SId,SUM(score) 总成绩,COUNT(CId)选课总数 from SC group by SId)
select S.SId 学生编号,
S.Sname 学生姓名,
A.选课总数,
A.总成绩
from Student S
left join A on A.SId = S.SId
---------------------------------------------------------------------------------------先join，再group by
select S.SId ,S.Sname ,COUNT(score) 选课总数,sum(score)总成绩
from Student S
left join SC C on S.SId=C.SId
group by S.SId ,S.Sname
go
--查有成绩的学生信息
------------------------------------------------------------------------------in
select *
from
Student
where SId in
(select distinct SId from SC)
------------------------------------------------------------------------any
select * from Student
where SId =
any (select SId from SC)
-------------------------------------------------------------------join，提高查询效率
select *
from Student S
inner join SC C on S.SId =C.SId
GO
--查询「李」姓老师的数量
------------------------------------------------------------------------like
select COUNT(TId)
from Teacher
where Tname like '李%'
go
--查询学过「张三」老师授课的同学的信息
---------------------------------------------------------------------------with套用
with
A as (select TId from Teacher where Tname='张三'),
B as (select CId from Course,A where A.TId = Course.TId),
C as (select SId from SC,B where SC.CId = B.CId )
select S.SId 学号,
S.Sname 姓名,
S.Sage 年龄,
S.Ssex 性别
from Student S,C where C.SId =S.SId
-----------------------------------------------------------------------多个select嵌套+in
select SId from SC where CId in (
select CId from Course where TId =
(select TId from Teacher where Tname='张三'))
go
--查询没有学全所有课程的同学的信息
----------------------------------------------------------------------not in + group by
select * from Student
where SId not in
(select SId from SC group by SId having COUNT(CId)=(select COUNT(CId) from Course))
----------------------------------------------------------------------all
select * from Student
where SId<>
all(select SId from SC group by SId having COUNT(CId)=(select COUNT(CId) from Course))
------------------------------------------------------------------------declare + except
declare @i int
set @i=(select COUNT(distinct CId) from Course);
with
A as (select SId from Student),
B as (select SId from SC group by SId having COUNT(CId)=@i)
select * from Student
where SId in
( select * from A
except
select * from B
)
go
--查询至少有一门课与学号为" 01 "的同学所学相同的同学的信息
--------------------------------------------------------------------------------in
select * from Student
where SId in
(select SId from SC where CId in
(select CId from SC where SId='01')
)
-----------------------------------------------------------------------------any
select * from Student
where SId in
(select SId from SC where CId = any
(select CId from SC where SId='01')
)
---------------------------------------------------------------------------with
with
A as (select CId from SC where SId = '01'),
B as (select SId from SC where CId = any (select * from A))
select * from Student
where SId in (select * from B)
go
--查询和" 01 "号的同学学习的课程 完全相同的其他同学的信息
-----------------------------------------------------------------------not in
with
A as (select CId from SC where SId='01'),
B as (select COUNT(CId) num from SC where SId='01')
select *
from Student
where SId not in(select SId from SC where CId not in (select * from A )) -----排除选课和01不同的学生
and SId in(select SId from SC group by SId having COUNT(CId)=(select * from B)) ----选择和01选课数相同的学生
and SId <>'01' ----排除01
-------------------------------------------------------------------------连接+exists
with
A as (select SId,CId from Student ,(select CId from SC where SId='01')a),
B as (select A.SId 学号,isnull(c.SId ,0)选课 from A left join SC c on A.CId =c.CId and A.SId =c.SId ),
C as (select distinct 学号 from B where 选课='0')
select * from Student S where not exists(select 1 from C where C.学号 =S.SId ) and SId <>'01'
go
--查询没学过"张三"老师讲授的任一门课程的学生姓名
-----------------------------------------------------------------------------------in+not in
with
A as (select TId from Teacher where Tname='张三'), --张三老师教师号
B as (select CId from Course,A where Course.TId = A.TId ), ---张三老师教的课
C as (select SId from SC where CId in (select * from B))--学过张三老师课的学生编号
select SId,Sname from Student where SId <>all
(select * from C)
go
--------------------------------------------------------------------------------left join
with
A as (select TId from Teacher where Tname='张三'), --张三老师教师号
B as (select CId from Course,A where Course.TId = A.TId ), ---张三老师教的课
C as (select SId,score from SC where CId = (select * from B)) ---上过张三老师课的学生以及成绩
select S.SId,S.Sname
from Student S
left join C on C.SId =S.SId --左连接
where score is null
go
--查询两门及其以上不及格课程的同学的学号，姓名及其平均成绩
---------------------------------------------------------------------笛卡尔积 + group by
with
A as (select SId,AVG(score)平均成绩 from SC where score<60 group by SId having COUNT(score)>=2)
select S.SId 学号,
S.Sname 姓名,
A.平均成绩
from Student S, A
where S.SId = A.SId
--------------------------------------------------------------------------------inner join
with
A as (select SId,AVG(score)平均成绩 from SC where score<60 group by SId having COUNT(score)>=2)
select S.SId,S.Sname,A.平均成绩
from Student s
inner join A on A.SId=s.SId
--------------------------------------------------------------------------------join + group by
select S.SId ,Sname,AVG(score) 平均成绩
from Student S
inner join SC C on S.SId =C.SId
where C.score <60
group by S.SId ,S.Sname
having COUNT(*)>=2
go
--检索" 01 "课程分数小于 60，按分数降序排列的学生信息
------------------------------------------------------------------------笛卡尔积
select s.SId,S.Sname,S.Ssex,score
from Student S,SC
where SC.SId =S.SId and CId='01' and score<60
order by score DESC
go
---------------------------------------------------------------------------join
with
A as (select SId,score from SC where CId='01' and score<60)
select s.SId ,S.Sname ,S.Ssex,A.score
from Student S
inner join A
on A.SId =S.SId
order by A.score DESC
go
--按平均成绩从高到低显示所有学生的所有课程的成绩以及平均成绩
-----------------------------------------------------------------------------left join + order by
with
A as (select SId,AVG(score)avgscore from SC group by SId),
B as (select S.*,CId,score from Student S left join SC C on S.SId =C.SId )
select B.SId ,B.Sname ,B.CId ,B.score ,A.avgscore
from B left join A on A.SId =B.SId
order by avgscore desc
GO
--查询各科成绩最高分、最低分和平均分：
--以如下形式显示：课程 ID，课程 name，最高分，最低分，平均分，及格率，中等率，优良率，优秀率
--及格为>=60，中等为：70-80，优良为：80-90，优秀为：>=90
--要求输出课程号和选修人数，查询结果按人数降序排列，若人数相同，按课程号升序排列
-------------------------------------------------------------------------------------------------
with
A as (select CId,AVG(score)平均分,MAX(score)最高分,MIN(score)最低分,COUNT(score)总数 from SC group by CId),
A1 as (select CId,COUNT(score)及格数 from SC where score>=60 group by CId ),
A2 as (select CId,COUNT(score)中等数 from SC where score>=70 and score<80 group by CId),
A3 as (select CId,COUNT(score)优良数 from SC where score>=80 and score<90 group by CId),
A4 as (select CId,COUNT(score)优秀数 from SC where score>=90 group by CId),
AAA as (select A.CId ,A.平均分 ,A.总数 ,isnull(A1.及格数,0)及格数 ,isnull(A2.中等数,0)中等数 ,isnull(A3.优良数,0) 优良数,isnull(A4.优秀数 ,0)优秀数,A.最低分 ,A.最高分
from A
left join A1 on A1.CId = A.CId
left join A2 on A2.CId = A.CId
left join A3 on A3.CId = A.CId
left join A4 on A4.CId = A.CId)
--select * from AAA
--select 及格数/总数 from AAA
select AAA.CId 课程ID,
Course.Cname 课程名,
AAA.平均分,
AAA.最高分,
AAA.最低分,
AAA.总数 选修人数,
cast(ROUND(cast(及格数 as float)/总数,2)*100 as varchar(10))+'%' 及格率,
cast(ROUND(cast(中等数 as float)/总数,2)*100 as varchar(10))+'%' 中等率,
cast(ROUND(cast(优良数 as float)/总数,2)*100 as varchar(10))+'%' 优良率,
cast(ROUND(cast(优秀数 as float)/总数,2)*100 as varchar(10))+'%' 优秀率
from AAA,Course
where AAA.CId = Course.CId
order by AAA.总数 DESC, AAA.CId ASC
---------------------------------------------------------------------------------------------------------group by + case when
with
A as (select CId,AVG(score)平均分,MAX(score)最高分,MIN(score)最低分,COUNT(score)总数,
CONVERT(varchar,CONVERT(decimal(18,1),100*sum(case when score>=60 then 1 else 0 end)/COUNT(*)))+'%' as 及格率,
CONVERT(varchar,CONVERT(decimal(18,1),sum(case when score>=70 and score<80 then 1 else 0 end)*100/COUNT(*)))+'%' as 中等率,
CONVERT(varchar,CONVERT(decimal(18,1),sum(case when score>=80 and score<90 then 1 else 0 end)*100/count(*)))+'%' as 优良率,
CONVERT(varchar,CONVERT(decimal(18,1),sum(case when score>=90 then 1 else 0 end)*100/count(*)))+'%' as 优秀率
from SC group by CId)
select Cname,A.* from Course ,A
where A.CId = Course .CId
order by A.总数 desc,A.CId asc
go
--按各科成绩进行排序，并显示排名， Score 重复时保留名次空缺
------------------------------------------------------------------------------------------------找到SC表内，所有比本条数据分数大的分数n条，那么这条序号就是n+1
select CId 课程号,
SId 学号,
(select COUNT(*) from SC where score>a.score and CId=a.CId )+1 rank
from SC a
order by CId,rank
-------------------------------------------------------------------------------------------------左连接，条件：课程号相同，且比本人分数高，分组计数
select a.cid 课程号,
a.sid 学号,
count(b.score)+1 as rank
from sc as a
left join sc as b on a.score<b.score and a.cid = b.cid
group by a.cid, a.sid,a.score
order by a.cid, rank ASC;
------------------------------------------------------------------------------------------rank：会把重复的排名一样
select CId 课程号,
SId 学号,
RANK() over(partition by CId order by score desc) as 排名
from SC
order by CId,排名
go
--按各科成绩进行排序，并显示排名， Score 重复时合并名次
---------------------------------------------------------------------------------------------找到SC表中，比本条分数大的所有不一样的分数n条，那么这条序号就是n+1
select CId 课程号,
SId 学号,
(select COUNT(distinct score) from SC where CId=t.CId and t.score>score )+1 rank
from SC t
order by
CId,
rank
----------------------------------------------------------------------------------------------dense_rank()函数
select CId 课程号,
SId 学号,
dense_RANK() over(partition by CId order by score desc) as 排名
from SC
order by CId,排名
-----------------------------------------------------------------------------------SC表左连接，计算比自己分数高的分数个数，只计算不同的分数，重复的不计入内
select a.cid 课程号,
a.sid 学号,
count(distinct b.score)+1 as rank
from sc as a
left join sc as b on a.score<b.score and a.cid = b.cid
group by a.cid, a.sid,a.score
order by a.cid, rank ASC;
go
--查询学生的总成绩，并进行排名，总分重复时保留名次空缺
------------------------------------------------------------------------------count()函数
with
A as (select SId,SUM(score)总分 from SC group by SId)
select SId,
总分,
(select COUNT(总分) from A where 总分>t.总分)+1 rank
from A t
order by rank
go
-------------------------------------------------------------------------------left join
with
A as (select SId,SUM(score)总分 from SC group by SId)
select A.SId 学号,
A.总分 ,
COUNT(B.总分)+1 排名
from A left join A B on A.总分<B.总分
group by A.SId ,A.总分
order by A.总分 desc
go
-----------------------------------------------------------------------------rank
with
A as (select SId,SUM(score)总分 from SC group by SId)
select A.*,RANK() over(order by 总分 desc)as rank from A order by rank
go
--查询学生的总成绩，并进行排名，总分重复时不保留名次空缺
-----------------------------------------------------------------------------count
with
A as (select SId,SUM(score)总分 from SC group by SId)
select SId,
总分,
(select COUNT(distinct 总分) from A where 总分>t.总分)+1 rank
from A t
order by rank
--------------------------------------------------------------------------------dense_rank
with
A as (select SId,SUM(score)总分 from SC group by SId)
select A.*,
DENSE_RANK() over(order by 总分 desc) rank
from A order by rank
----------------------------------------------------------------------------------join
with
A as (select SId,SUM(score)总分 from SC group by SId)
select A.*,COUNT(distinct B.总分)+1 as rank
from A left join A B on A.总分<B.总分
group by A.SId ,A.总分
order by rank
go
--统计各科成绩各分数段人数：课程编号，课程名称，[100-85]，[85-70]，[70-60]，[60-0] 及所占百分比
go
with
A as (select CId ,count(score)总数 from SC group by CId),
A1 as (select CId,COUNT(score)不及格数 from SC where score<60 group by CId ),
A2 as (select CId,COUNT(score)及格数 from SC where score>=60 and score<70 group by CId ),
A3 as (select CId,COUNT(score)中等数 from SC where score>=70 and score<85 group by CId),
A4 as (select CId,COUNT(score)优秀数 from SC where score>=85 and score<100 group by CId),
AAA as (select A.CId ,A.总数 ,isnull(A1.不及格数,0)不及格数 ,isnull(A2.及格数,0)及格数 ,isnull(A3.中等数,0) 中等数,isnull(A4.优秀数 ,0)优秀数
from A
left join A1 on A1.CId = A.CId
left join A2 on A2.CId = A.CId
left join A3 on A3.CId = A.CId
left join A4 on A4.CId = A.CId)
select AAA.CId 课程编号,
Course.Cname 课程名称,
AAA.不及格数 [60-0] ,
cast(ROUND(cast(不及格数 as float)/总数,2)*100 as varchar(10))+'%' [占比],
AAA.及格数 [60-70] ,
cast(ROUND(cast(及格数 as float)/总数,2)*100 as varchar(10))+'%' [占比],
AAA.中等数 [70-85],
cast(ROUND(cast(中等数 as float)/总数,2)*100 as varchar(10))+'%' [占比],
AAA.优秀数 [85-100],
cast(ROUND(cast(优秀数 as float)/总数,2)*100 as varchar(10))+'%' [占比],
AAA.总数 选课总人数
from AAA,Course
where AAA.CId = Course.CId
order by AAA.总数 DESC, AAA.CId ASC
go
------------------------------------------------------------------------------------------------convert
select SC.CId,Cname,
sum(case when score<60 then 1 else 0 end) as 人数,
convert(varchar,CONVERT(decimal(18,1),sum(case when score<60 then 1 else 0 end)*100/COUNT(*)))+'%' as [0-60],
sum(case when score>=60 and score<70 then 1 else 0 end) as 人数,
convert(varchar,CONVERT(decimal(18,1),sum(case when score>=60 and score<70 then 1 else 0 end)*100/COUNT(*)))+'%' as [60-70],
sum(case when score>=70 and score<85 then 1 else 0 end) as 人数,
convert(varchar,CONVERT(decimal(18,1),sum(case when score>=70 and score<85 then 1 else 0 end)*100/COUNT(*)))+'%' as [70-85],
sum(case when score>=85 then 1 else 0 end) as 人数,
convert(varchar,CONVERT(decimal(18,1),sum(case when score>=85 then 1 else 0 end)*100/COUNT(*)))+'%' as [85以上]
from SC,Course where SC.CId =Course.CId
group by SC.CId,Cname
order by SC.CId
go
--查询各科成绩前三名的记录
-------------------------------------------------------------------------------------------------row_number+partition
with
A as (select *,ROW_NUMBER() over(partition by CId order by score desc) as num from SC )
select * from A where num<=3
order by CId,score desc
-----------------------------------------------------------------------------------------------查询效率最低
select a.CId ,a.SId ,a.score
from SC a
where a.SId in(select top 3 SId
from SC b
where a.CId =b.CId
order by score desc
)
order by CId,score desc
------------------------------------------------------------------------------------------------------------成绩重复的情况下，会出现bug,比如99,88,88,88,88,77
select * from SC a
where (select count(*) from SC b where a.CId =b.CId and b.score>a.score)<3
order by CId,score desc
go
--查询每门课程被选修的学生数
select CId ,COUNT(SId) from SC group by CId
go
--查询出只选修两门课程的学生学号和姓名
-----------------------------------------------------------in 判断
select Sname 姓名,
SId 学号
from Student
where SId in
(select SId from SC group by SId having COUNT(CId)=2)
--------------------------------------------------------------join 连接
select S.Sname,S.SId
from Student S
inner join SC on SC.SId =S.SId
group by Sname,S.SId
having COUNT(CId)=2
go
--查询男生、女生人数
select Ssex,COUNT(Ssex)
from Student
GROUP BY Ssex
go
--查询名字中含有「风」字的学生信息
select * from Student where Sname like '%风%'
go
--查询同名同性学生名单，并统计同名人数
----------------------------------------------------------------------先with子查询，再对临时表进行分组
with
A as (select SId,Sname,Ssex from Student t where Sname = any (select Sname from Student where Sname=t.Sname and t.Ssex= Ssex and SId!=t.SId )),
B as (select Sname,COUNT(Sname)人数 from A group by Sname)
select A.* ,B.人数 from A join B on A.Sname =B.Sname order by A.Sname
------------------------------------------------------------------------group by 两个字段 + 笛卡尔积
select S.*,C.num
from Student S,
(select Sname,Ssex,COUNT(Sname) num from Student group by Sname,Ssex)C
where S.Sname =C.Sname and C.num >1
-----------------------------------------------------------------------------group by 两个字段 + exists
select * from Student a
where exists(
select Sname ,COUNT(*)
from Student b
where b.Sname =a.Sname
group by Sname ,Ssex
having COUNT(*)>1
)
go
--查询 1990 年出生的学生名单
--------------------------------------------------------------------通过范围查找
select * from Student where Sage >= '1990' and Sage<'1991'
----------------------------------------------------------------------通过datepart查找
select * from Student where DATEPART(YY,Sage)='1990'
go
--查询每门课程的平均成绩，结果按平均成绩降序排列，平均成绩相同时，按课程编号升序排列
select CId,
AVG(score)平均成绩
from SC
group by CId
order by 平均成绩 desc,
CId
go
--查询平均成绩大于等于 85 的所有学生的学号、姓名和平均成绩
-------------------------------------------------------------------------------------------inner join
select S.SId,Sname,C.平均成绩
from Student S
inner join
(select SId,AVG(score)平均成绩 from SC group by SId having AVG(score)>=85)C
on S.SId =C.SId
go
--------------------------------------------------------------------------------------------笛卡尔积
with
A as (select SId,AVG(score)平均成绩 from SC group by SId having AVG(score)>=85)
select Student.Sname,Student.SId ,A.平均成绩
from Student ,A
where Student.SId =A.SId
go
--查询课程名称为「数学」，且分数低于 60 的学生姓名和分数
------------------------------------------------------------------------------------------with
with
A as (select CId from Course where Cname='数学'),
B as (select SId,score from SC where CId in (select * from A) and score <60)
select Sname,B.score
from Student ,B
where Student.SId =B.SId
go
--查询所有学生的课程及分数情况（存在学生没成绩，没选课的情况）
----------------------------------------------------------------------------------------------left join
select a.Sname ,c.Cname ,b.score
from Student a
left join SC b on a.SId =b.SId
left join Course c on c.CId = b.CId
order by c.CId desc
go
------------------------------------------------------------------------------------------------------with left join
with
A as (select Student.SId,Sname,CId,Cname from Student ,Course),
B as (select A.* ,SC.score from A left join SC on SC.CId = A.CId and SC.SId =A.SId )
select * from B
-----------------------------------------------------------------------------------------------------left join
select b.Sname,Course.Cname,b.score
from
(select a.SId,a.Sname,sc.cid,sc.score
from
(select sid,Sname,cid
from Student,Course) a left join sc
on a.sid=sc.sid and a.cid=sc.cid) b left join Course on b.cid=Course.cid
--查询任何一门课程成绩在 70 分以上的姓名、课程名称和分数
------------------------------------------------------------------------------------只要有70分，都算上
with
A as (select * from SC where score>70),
B as (select A.*,Course.Cname from A left join Course on A.CId =Course.CId),
C as (select S.Sname,B.Cname,B.score from B left join Student S on B.SId=S.SId)
select * from C
-----------------------------------------------------------------------------------------最低分也有70分,left join，笛卡尔积
go
with
A as (select SId from SC group by SId having MIN(score)>70),
B as (select SC.* from SC inner join A on SC.SId = A.SId),
C as (select S.Sname,Co.Cname ,B.score from B,Student S,Course Co where S.SId =B.SId and B.CId = Co.CId)
select * from C
----------------------------------------------------------------------------------------笛卡尔积
select s.Sname,c.Cname,sc.score
from Student s,Course c,SC
where s.SId=sc.SId and c.CId=sc.CId
and sc.score>=70
go
--查询不及格的课程
with
A as (select CId,score from SC where score<60),
B as (select C.Cname,A.score from A,Course C where A.CId =C.CId)
select * from B
order by B.Cname
go
select DISTINCT Course.Cname
from SC,Course
where sc.CId=Course.CId and sc.score<60
--查询课程编号为 01 且课程成绩在 80 分以上的学生的学号和姓名
with
A as (select SId from SC where CId='01' and score>80)
select SId,Sname
from Student
where SId in
(select * from A)
go
--求每门课程的学生人数
select CId,COUNT(SId) from SC group by CId
go
--成绩不重复，查询选修「李四」老师所授课程的学生中，成绩最高的学生信息及其成绩（排除重复成绩）
----------------------------------------------------------------------------------------------------正确的
with
A as (select TId from Teacher where Tname='李四'),--李四的教师号
B as (select CId from Course where TId in (select * from A)), --01课程
C as (select SId ,score from SC where CId = (select CId from B)), --01课程成绩表
E as (select t.SId from C t left join C p on t.score =p.score group by t.SId having COUNT(*)=1) ,--排除课程表重复选项
F as (select top 1*from C where exists(select SId from E where E.SId =C.SId ) order by score desc)
select * from Student ,F where Student.SId = F.SId
-----------------------------------------------------------------笛卡尔积计算无重复最高分
with
A as(select top 1 score
from SC,Course,Teacher
where SC.CId =Course.CId
and Course.TId = Teacher.TId
and Teacher.Tname='李四'
group by score
having COUNT(*)=1
order by score desc
)
select * from A
go
--成绩有重复的情况下，查询选修「李四」老师所授课程的学生中，成绩最高的学生信息及其成绩
-------------------------------------------------------------------------------------------------------------
with
A as (select CId from Course where TId in (select TId from Teacher where Tname='李四')),--李四老师的课程
B as (select MAX(score)最高分 from SC where CId in (select * from A)),--李四老师课程的最高分
D as (select SId from SC where score in (select * from B) and CId in (select * from A)) --最高分的学号
select S.* ,
C.score
from Student S,SC C
where S.SId = C.SId
and C.SId in (select * from D)
and C.CId in (select * from A)
go
----------------------------------------------------------------------------------------更直观，易于读
SELECT s.*,
sc.score
FROM Student s,
Course c,
Teacher t,
SC
WHERE s.SId= sc.SId
and c.CId= sc.CId
and t.TId= c.TId and t.Tname='李四'
and sc.score=(select MAX(score)
from Student s,
Course c,
Teacher t,
SC
where s.SId= sc.SId
and c.CId= sc.CId
and t.TId= c.TId
and t.Tname='李四')
--------------------------------------------------------------------------------------------------rank/dense_rank + partition
go
with
A as (select TId from Teacher where Tname='李四'),
B as (select CId from Course where TId in (select * from A)),
C as (select *,RANK() over(partition by CId order by score desc) as num from SC)
select S.*,C.score
from Student S,C
where C.num =1 and C.CId in (select * from B) and S.SId =C.SId
go
--查询不同课程成绩相同的学生的学生编号、课程编号、学生成绩
-----------------------------------------------------------------------------A同学有两门课程相等
with
A as(select score,SId from SC group by score,SId having COUNT(CId)>1 )
select * from SC ,A
where SC.SId = A.SId
and SC.score = A.score
------------------------------------------------------------------------------SC表自连接
select * from SC
where SId in
(select a.SId from SC a
inner join SC b on a.CId <>b.CId and a.SId =b.SId and a.score =b.score)
go
--查询每门功课成绩最好的前两名
------------------------------------------------------------------------------------------row_number over（partition）
select CId,SId,score
from (select *,ROW_NUMBER() over(partition by CId order by score desc) as number from SC)a
where number<=2
order by CId,score desc
--------------------------------------------------------------------------------------------嵌套查询，复杂度高
select CId,
SId,
score
from SC a
where SId in(select top 2 SId
from SC b
where a.CId =b.CId
order by b.score
)
order by CId,score desc
-----------------------------------------------------------------------------------------利用比第一名第二名，成绩高的不超过两个，原理查询,存在bug:成绩重复，就会吧把所有的重复项都选上
select CId,SId,score from SC a
where (select COUNT(*) from SC b where a.CId =b.CId and b.score >a.score)<2
order by CId,score desc
go
--统计每门课程的学生选修人数（超过 5 人的课程才统计）
----------------------------------------------------------------------------group by
select SC.CId,
COUNT(SId)number,
Cname
from SC ,Course
where SC.CId = Course.CId
group by SC.CId ,Cname
having COUNT(SId)>5
--检索至少选修两门课程的学生学号
select SId from SC group by SId having COUNT(CId)>=2
go
--查询选修了全部课程的学生信息
--------------------------------------------------------------------with
with
A as (select COUNT(distinct CId)num from Course),
B as (select SId from SC group by SId having COUNT(CId) in (select * from A))
select * from Student
where SId in
(select * from B)
------------------------------------------------------------------------笛卡尔积
go
select Sname
from sc,Student
where sc.SId=Student.SId
group by Sname
having count(*)=(select count(*) from Course)
--查询各学生的年龄，只按年份来算
select SId,Sname,datediff(YY,Sage,GETDATE()) from Student
go
--按照出生日期来算，当前月日 < 出生年月的月日则，年龄减一
----------------------------------------------------------------------------
with
A as (select datepart(mm,GETDATE())月,DATEPART(DAY,GETDATE())日),
B as (select S.SId,Sname,datediff(YY,Sage,GETDATE())-1 年龄
from Student S,A
where datepart(mm,Sage)<A.月
or (datepart(mm,Sage)=A.月 and datepart(dd,Sage)<A.日)),
C as (select SId,Sname,datediff(YY,Sage,GETDATE()) 年龄
from Student
where SId not in
(select SId from B))
select * from B
union all
select * from C
order by SId
----------------------------------------------------------------------------
SELECT
SId 学号,
Sname 姓名,
CASE WHEN MONTH(Sage) < MONTH(GETDATE()) OR MONTH(Sage) = MONTH(GETDATE()) AND DAY(Sage) < DAY(GETDATE())
THEN YEAR(GETDATE()) - YEAR(Sage)-1
ELSE YEAR(GETDATE()) - YEAR(Sage)
END AS 年龄
FROM
Student
----------------------------------------------------------------------------
go
--查询本周过生日的学生
select Sname from Student where DATEPART(WEEK,Sage)=DATEPART(week,GETDATE())
go
--查询下周过生日的学生
select Sname from Student where DATEPART(WEEK,Sage)=DATEPART(week,GETDATE())+1
go
--查询本月过生日的学生
select Sname from Student where DATEPART(mm,Sage)=DATEPART(mm,GETDATE())
select Sname from Student where MONTH(Sage) = MONTH(GETDATE())
go
--查询下月过生日的学生
select a.Sname
from (
select sid,Sname,
CASE WHEN DATEPART(month, GETDATE())=12 THEN 1
ELSE DATEPART(month, GETDATE())+1
END as nextMonth,DATEPART(month, Sage) birthday
from Student) a
where a.birthday=a.nextMonth
扫一扫
3022
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
