共计 33977 个字符，预计需要花费 85 分钟才能阅读完成。
这篇文章将为大家详细讲解有关常用 SQL 语句有哪些，丸趣 TV 小编觉得挺实用的，因此分享给大家做个参考，希望大家阅读完这篇文章后可以有所收获。
说明：以下五十个语句都按照测试数据进行过测试，最好每次只单独运行一个语句。
问题及描述：
–1. 学生表
Student(Sid,Sname,Sage,Ssex)–Sid 学生编号,Sname 学生姓名,Sage 出生年月,Ssex
学生性别
–2. 课程表
Course(Cid,Cname,Tid)–Cid – 课程编号,Cname 课程名称,Tid 教师编号
–3. 教师表
Teacher(Tid,Tname) –Tid
教师编号,Tname 教师姓名
–4. 成绩表
SC(Sid,Cid,score) –Sid
学生编号,Cid 课程编号,score 分数
*/
– 创建测试数据
createtable Student(Sidvarchar(10),Snamenvarchar(10),Sagedatetime,Ssex
nvarchar(10))
insertinto Studentvalues(01
, N 赵雷 ,
1990-01-01 , N 男 )
insertinto Studentvalues(02
, N 钱电 ,
1990-12-21 , N 男 )
insertinto Studentvalues(03
, N 孙风 ,
1990-05-20 , N 男 )
insertinto Studentvalues(04
, N 李云 ,
1990-08-06 , N 男 )
insertinto Studentvalues(05
, N 周梅 ,
1991-12-01 , N 女 )
insertinto Studentvalues(06
, N 吴兰 ,
1992-03-01 , N 女 )
insertinto Studentvalues(07
, N 郑竹 ,
1989-07-01 , N 女 )
insertinto Studentvalues(08
, N 王菊 ,
1990-01-20 , N 女 )
createtable Course(Cidvarchar(10),Cnamenvarchar(10),Tidvarchar(10))
insertinto Coursevalues(01
, N 语文 ,
02 )
insertinto Coursevalues(02
, N 数学 ,
01 )
insertinto Coursevalues(03
, N 英语 ,
03 )
createtable Teacher(Tidvarchar(10),Tnamenvarchar(10))
insertinto Teachervalues(01
, N 张三 )
insertinto Teachervalues(02
, N 李四 )
insertinto Teachervalues(03
, N 王五 )
createtable SC(Sidvarchar(10),Cidvarchar(10),scoredecimal(18,1))
insertinto SCvalues(01
, 01 , 80)
insertinto SCvalues(01
, 02 , 90)
insertinto SCvalues(01
, 03 , 99)
insertinto SCvalues(02
, 01 , 70)
insertinto SCvalues(02
, 02 , 60)
insertinto SCvalues(02
, 03 , 80)
insertinto SCvalues(03
, 01 , 80)
insertinto SCvalues(03
, 02 , 80)
insertinto SCvalues(03
, 03 , 80)
insertinto SCvalues(04
, 01 , 50)
insertinto SCvalues(04
, 02 , 30)
insertinto SCvalues(04
, 03 , 20)
insertinto SCvalues(05
, 01 , 76)
insertinto SCvalues(05
, 02 , 87)
insertinto SCvalues(06
, 01 , 31)
insertinto SCvalues(06
, 03 , 34)
insertinto SCvalues(07
, 02 , 89)
insertinto SCvalues(07
, 03 , 98)
go
–1、查询 01 课程比 02 课程成绩高的学生的信息及课程分数
思路：课程 01（一个记录集合），课程 02（一个记录集合），STUDENT 表（一个记录集合），包含在这三个记录集合里，并且 01 分数 02 分数的记录。
select*
fromstudent s inner
join(select*
from sc where cid= 01 ) a
on s.sid=a.sidinnerjoin
(select*
from sc where cid= 02 ) b
on s.sid=b.sidwherea.score b.score
select a.*,b.*,c.*fromstudent a
innerjoinsc b
on a.sid=b.sidandb.cid= 01 inner
join sc c
on a.sid=c.sidandc.cid= 02 where
b.score c.score
–1.1、查询同时存在 01 课程和 02 课程的情况
思路：课程 01（一个记录集合），课程 02（一个记录集合），STUDENT 表（一个记录集合），包含在这三个记录集合里的记录。
select*
fromstudent s inner
join(select*
from sc where cid= 01 ) a
on s.sid=a.sidinnerjoin
(select
* from sc where cid= 02 ) b
on s.sid=b.sidwherea.sid=b.sid
select s.*,a.*,b.*fromstudent s
innerjoinsc a
on s.sid=a.sidanda.cid= 01 inner
joinsc b
on s.sid=b.sidandb.cid= 02
select a.* , b.score[课程 01 的分数],c.score[课程 02 的分数]from
Student a , SC b , SC c
where a.Sid= b.Sid
and a.Sid= c.Sid
and b.Cid= 01 and c.Cid= 02 and
b.score c.score
–1.2、查询同时存在 01 课程和 02 课程的情况和存在 01 课程但可能不存在 02 课程的情况(不存在时显示为 null)(以下存在相同内容时不再解释)
思路：课程 01（一个记录集合），课程 02 可能有，可能不存在（cid=’02’or cid is null）,STUDENT 表（一个记录集合）
select*
fromstudent s inner
joinsc a
on s.sid=a.sidanda.cid= 01 left
join sc b
on s.sid=b.sidand(b.cid= 02 or
b.cid is
null) where a.score isnull(b.score,0)
select a.* , b.score[课程 01 的分数],c.score[课程 02 的分数]from
Student a leftjoin SC b
on a.Sid= b.Sid
and b.Cid= 01 leftjoin SC c
on a.Sid= c.Sid
and c.Cid= 02
where b.score isnull(c.score,0)
–2、查询 01 课程比 02 课程成绩低的学生的信息及课程分数
select*
fromstudent s inner
joinsc a
on s.sid=a.sidanda.cid= 01 inner
join sc b
on s.sid=b.sidandb.cid= 02 where
a.score b.score
–2.1、查询同时存在 01 课程和 02 课程的情况
select a.* , b.score[课程 01 的分数],c.score[课程 02 的分数]from
Student a , SC b , SC c
where a.Sid= b.Sid
and a.Sid= c.Sid
and b.Cid= 01 and c.Cid= 02 and
b.score c.score
–2.2、查询同时存在 01 课程和 02 课程的情况和不存在 01 课程但存在 02 课程的情况
select*
fromstudent s left
joinsc a
on s.sid=a.sidand(a.cid= 01 or
a.cid is
null) innerjoin sc b
on s.sid=b.sidandb.cid= 02
select*
fromstudent s inner
join
(select*
from sc where cid= 02 ) aon s.sid=a.sidleft
join
(select*
from sc where
(cid= 01 or cid
is null)) b
on s.sid=b.sid
select a.* , b.score[课程 01 的分数],c.score[课程 02 的分数]from
Student a
leftjoin SC bon a.Sid
= b.Sid and b.Cid= 01
leftjoin SC con a.Sid
= c.Sid and c.Cid= 02
whereisnull(b.score,0)
c.score
–3、查询平均成绩大于等于 60 分的同学的学生编号和学生姓名和平均成绩
思路：平均成绩大于等于 60 分（一个记录集合），STUDENT 表（一个记录集合）
select s.sid,s.sname,b.[平均成绩]fromstudent
s innerjoin
(select sid,convert(decimal(18,2),avg(score))as
平均成绩 from sc
group by sid
having avg(score) =60) b
on s.sid=b.sid
select*
fromstudent s inner
join
(select sid,avg(score)as
avgscore from scgroup
by sid having
avg(score) =60) a
on s.sid=a.sid
select a.Sid , a.Sname ,cast(avg(b.score)asdecimal(18,2))
avg_score
from Student a , sc b
where a.Sid= b.Sid
groupby a.Sid , a.Sname
havingcast(avg(b.score)asdecimal(18,2)) =60
orderby a.Sid
–4、查询平均成绩小于 60 分的同学的学生编号和学生姓名和平均成绩
思路：平均成绩小于 60 分（一个记录集合），STUDENT（一个记录集合）
select s.sid,s.sname,b.[平均成绩]fromstudent
s innerjoin
(select sid,convert(decimal(18,2),avg(score))as
平均成绩 from sc
group by sid
having avg(score) 60) b
on s.sid=b.sid
–4.1、查询在 sc 表存在成绩的学生信息的 SQL 语句。
思路：STUDENT 表（一个记录集合）是否有记录包含在 SC 表（一个记录集合）
select*
fromstudent where sid
in(select sidfrom sc)
select*
fromstudent s where
exists(select 1from sc a
where s.sid=a.sid)
select a.Sid , a.Sname ,cast(avg(b.score)asdecimal(18,2))
avg_score
from Student a , sc b
where a.Sid= b.Sid
groupby a.Sid , a.Sname
havingcast(avg(b.score)asdecimal(18,2)) 60
orderby a.Sid
–4.2、查询在 sc 表中不存在成绩的学生信息的 SQL 语句。
select
* from student where sid not
in (select distinct sid from sc)
select*
fromstudent s where
notexists(select 1
from sc a where s.sid=a.sid)
select a.Sid , a.Sname ,isnull(cast(avg(b.score)asdecimal(18,2)),0)
avg_score
from Student aleftjoin sc b
on a.Sid= b.Sid
groupby a.Sid , a.Sname
havingisnull(cast(avg(b.score)asdecimal(18,2)),0) 60
orderby a.Sid
–5、查询所有同学的学生编号、学生姓名、选课总数、所有课程的总成绩
思路：SC 表的选课总数、总成绩（一个记录集合），STUDENT 表（一个记录集合）
select s.sid,s.sname,a.[选课总数],a.[总成绩]fromstudent
s innerjoin
(select sid,count(*)as
选课总数 ,sum(score)as
总成绩 from sc
group by sid) a
on s.sid=a.sid
select*
fromstudent s inner
join
(select sid,count(cid)as
课程总数 ,sum(score)as
课程总成绩 from sc
group by sid) a
on s.sid=a.sid
select s.sid,s.sname,count(a.cid)as
课程总数 ,sum(a.score)as
课程总成绩 from student s
innerjoin sc a
on s.sid=a.sidgroupby
s.sid,s.sname
–5.1、查询所有有成绩的 SQL。
select s.sid,s.sname,a.[选课总数],a.[总成绩]fromstudent
s innerjoin
(select sid,count(*)as
选课总数 ,sum(score)as
总成绩 from sc
group by sid) a
on s.sid=a.sid
select a.Sid[学生编号],
a.Sname[学生姓名],count(b.Cid)
选课总数,sum(score)
[所有课程的总成绩]
from Student a , SC b
where a.Sid= b.Sid
groupby a.Sid,a.Sname
orderby a.Sid
–5.2、查询所有 (包括有成绩和无成绩) 的 SQL。
select s.sid,s.sname,a.[选课总数],a.[总成绩]fromstudent
s leftjoin
(select sid,count(*)as
选课总数 ,sum(score)as
总成绩 from sc
group by sid) a
on s.sid=a.sid
select*
fromstudent s left
join
(select sid,count(cid)as
课程总数 ,sum(score)as
课程总成绩 from sc
group by sid) a
on s.sid=a.sidorderby
s.sid
select s.sid,s.sname,count(a.cid)as
课程总数 ,sum(a.score)as
课程总成绩 from student s
leftjoin sc a
on s.sid=a.sidgroupby
s.sid,s.snameorder
by s.sid
select a.Sid[学生编号],
a.Sname[学生姓名],count(b.Cid)
选课总数,sum(score)
[所有课程的总成绩]
from Student aleftjoin SC b
on a.Sid= b.Sid
groupby a.Sid,a.Sname
orderby a.Sid
–6、查询 李 姓老师的数量
select count(*) as 数量 fromteacher where left(tname,1)= 李
– 方法 1
selectcount(Tname)[李 姓老师的数量]from
Teacher where Tnamelike N 李 %
– 方法 2
selectcount(Tname)[李 姓老师的数量]from
Teacher whereleft(Tname,1)=
N 李
–7、查询学过 张三 老师授课的同学的信息
思路：STUDENT（一个记录集合），张三老师（一个记录集合），张三老师上的课（一个记录集合），张三老师上的课的成绩（一个记录集合）
select*
fromstudent s inner
joinsc a
on s.sid=a.sidinnerjoin
course c
on a.cid=c.cidinnerjoin
teacher t
on c.tid=t.tidwheret.tname= 张三
思路：从全部学生中（一个记录集合）提取上过张三老师课的学生（一个记录集合）
select*
fromstudent where sid
in(
select sidfrom sc a
inner join course b
on a.cid=b.cidinnerjoin
teacher c
on b.tid=c.tidandc.tname= 张三 )
selectdistinct Student.*from
Student , SC , Course , Teacher
where Student.Sid= SC.Sid
and SC.Cid
= Course.Cidand Course.Tid
= Teacher.Tid
and Teacher.Tname= N 张三
orderby Student.Sid
–8☆、查询没学过 张三 老师授课的同学的信息
思路：从全部学生中（一个记录集合）删除上过张三老师课的学生（一个记录集合）。
select*
fromstudent where sid
notin(
select distinct sid
from sc a inner
join course c
on a.cid=c.cidinnerjoin
teacher t
on c.tid=t.tidwheret.tname= 张三 )
select m.*from Student mwhere
Sid notin (selectdistinct SC.Sidfrom
SC , Course , Teacherwhere SC.Cid
= Course.Cid
and Course.Tid= Teacher.Tid
and Teacher.Tname
= N 张三 )orderby
m.Sid
–9、查询学过编号为 01 并且也学过编号为 02 的课程的同学的信息
思路：上过课程 01（一个记录集合），上过课程 02（一个记录集合），STUDENT 表（一个记录集合）
select*
fromstudent s inner
joinsc a
on s.sid=a.sidanda.cid= 01 inner
join sc b
on s.sid=b.sidandb.cid= 02
思路：上过课程 01 的学生（一个记录集合）并且存在上过课程 02 的学生（一个记录集合）
select*
fromstudent s inner
joinsc a
on s.sid=a.sidanda.cid= 01 and
exists (select 1
from sc bwhere s.sid=b.sidand b.cid= 02 )
– 方法 1
select Student.*from Student , SCwhere
Student.Sid= SC.Sid
and SC.Cid
= 01 andexists (Select1from
SC SC_2 where SC_2.Sid= SC.Sid
and SC_2.Cid
= 02 )orderby Student.Sid
– 方法 2
select Student.*from Student , SCwhere
Student.Sid= SC.Sid
and SC.Cid
= 02 andexists (Select1from
SC SC_2 where SC_2.Sid= SC.Sid
and SC_2.Cid
= 01 )orderby Student.Sid
– 方法 3
select m.*from Student mwhere
Sid in
(
select Sid from
(
selectdistinctSidfrom
SC where Cid= 01
unionall
selectdistinctSidfrom
SC where Cid= 02
) t groupby Sidhavingcount(1)=2
)
orderby m.Sid
–10☆、查询学过编号为 01 但是没有学过编号为 02 的课程的同学的信息
思路：上过课程 01 的学生（一个记录集合）并且不存在上过课程 02 的学生（一个记录集合）
select*
fromstudent s inner
joinsc a
on s.sid=a.sidanda.cid= 01 and
not exists
(select 1from sc b
where s.sid=b.sidand b.cid= 02 )
思路：从全部学生中（一个记录集合）先提取上过课程 01 的学生记录（一个记录集合）再排除没上过课程 02 的学生记录（一个记录集合）
select*
fromstudent where sid
in
(select sidfrom sc
where cid= 01 )and sid
not in
(
select sidfrom sc
where cid= 02 )
select*
fromstudent s inner
joinsc a
on s.sid=a.sidanda.cid= 01 where
s.sid not
in (select sidfrom sc
where cid= 02 )
– 方法 1
select Student.*from Student , SCwhere
Student.Sid= SC.Sid
and SC.Cid
= 01 andnotexists (Select1from
SC SC_2where SC_2.Sid
= SC.Sid
and SC_2.Cid= 02 )orderby
Student.Sid
– 方法 2
select Student.*from Student , SCwhere
Student.Sid= SC.Sid
and SC.Cid
= 01 and Student.Sidnotin (Select
SC_2.Sidfrom SC SC_2
where SC_2.Sid
= SC.Sidand SC_2.Cid
= 02 )orderby Student.Sid
–11、查询没有学全所有课程的同学的信息
思路：从全部学生中（一个记录集合）提取在 SC 表中课程总数不是全部的学生（一个记录集合）
select*
fromstudent where sid
in
(select sidfrom
(select sid,count(*)as
abc from sc group
by sid havingcount(*) (selectcount(*)
from course)) t)
该方法只列出有课程分数的学生，一个课程分数也没有的学生不存在第二个记录集合中。
思路：从全部学生中（一个记录集合）排除在 SC 表中有全部课程分数的学生（一个记录集合）
select*
fromstudent where sid
notin
(select sidfrom
(select sid,count(*)as
abc from sc group
by sid havingcount(*)=(selectcount(*)
from course)) t)
该方法还会列出一个课程分数都没有的学生。
–11.1、
select Student.*
from Student , SC
where Student.Sid= SC.Sid
groupby Student.Sid , Student.Sname ,Student.Sage , Student.Ssexhavingcount(Cid)
(selectcount(Cid)from Course)
–11.2
select Student.*
from Studentleftjoin SC
on Student.Sid= SC.Sid
groupby Student.Sid , Student.Sname ,Student.Sage , Student.Ssexhavingcount(Cid)
(selectcount(Cid)from Course)
–12、查询至少有一门课与学号为 01 的同学所学相同的同学的信息
思路：从全部学生中（一个记录集合）提取所学课程中至少有一门和学生 01 所学课程相同（一个记录集合）（也就是课程 ID 至少有一个存在于学生 01 的课程 ID 中）并排除学生 01
select*
fromstudent where sid
in
(selectdistinct sid
from scwhere cid
in
(select cidfrom sc
where sid= 01 )and sid 01 )
selectdistinct Student.*from
Student , SC where Student.Sid= SC.Sid
and SC.Cid
in (select Cidfrom SC
where Sid= 01 )and Student.Sid
01
–13☆、查询和 01 号的同学学习的课程完全相同的其他同学的信息
思路：从全部学生中（一个记录集合）提取所学全部课程 ID 存在于学生 01 的课程 ID 中并且课程总数等于学生 01 的课程总数（一个记录集合）
select*
fromstudent where sid
in
(selectdistinct sid
from scwhere cid
in
(select cidfrom sc
where sid= 01 )and sid 01 group
by sid
havingcount(*)=(selectcount(*)
from sc where sid= 01 ))
select Student.*from Studentwhere
Sid in
(selectdistinct SC.Sidfrom
SC where Sid 01 and SC.Cidin
(selectdistinct Cidfrom SC
where Sid= 01 )
groupby SC.Sidhavingcount(1)=
(selectcount(1)from
SC where Sid= 01 ))
–14、查询没学过 张三 老师讲授的任一门课程的学生姓名
思路：从全部学生中（一个记录集合）排除学过老师张三上过的课的学生（一个记录集合）（就是在 SC 表中有张三老师上过的课的分数）
select*
fromstudent where sid
notin
(selectdistinct a.sid
from sc a inner
join course b
on a.cid=b.cidinnerjoin
teacher c
on b.tid=c.tidwherec.tname= 张三 )
select student.*from studentwhere
student.Sidnotin
(selectdistinct sc.Sidfrom
sc , course , teacherwhere sc.Cid
= course.Cid
and course.Tid= teacher.Tid
and teacher.tname
= N 张三 )
orderby student.Sid
–15☆、查询两门及其以上不及格课程的同学的学号，姓名及其平均成绩
思路：全部学生（一个记录集合），两门及以上不及格课程（一个记录集合）
select*
fromstudent s inner
join
(select sid,count(*)as
不及格课程总数 ,convert(decimal(18,2),avg(score))as
平均分数 from sc
where score 60group
by sid having
count(*) =2) b
on s.sid=b.sid
select s.sid,s.sname,convert(decimal(5,2),avg(a.score))as
average fromstudent sinner
joinsc a
on s.sid=a.sidgroupby
s.sid,s.snamehaving s.sid
in
(select sidfrom
(select sid,count(*)as
times from sc where score 60
groupby sid
having count(*) =2) t)
select student.Sid ,student.sname ,cast(avg(score)asdecimal(18,2))
avg_score from student , sc
where student.Sid= SC.Sid
and student.Sid
in (select Sidfrom SC
where score 60groupby
Sidhavingcount(1) =2)
groupby student.Sid , student.sname
–16、检索 01 课程分数小于 60，按分数降序排列的学生信息
思路：全部学生（一个记录集合），课程 01 分数小于 60（一个记录集合）
select*
fromstudent s inner
joinsc a
on s.sid=a.sidwherecid= 01 and
score 60 order
by score desc
select*
fromstudent s inner
join(select*
from sc where cid= 01 and score 60)
a
on s.sid=a.sidorderby
a.score
select student.* , sc.Cid , sc.scorefrom
student , sc
where student.Sid= SC.Sid
and sc.score
60and sc.Cid= 01
orderby sc.scoredesc
–17☆☆☆、按平均成绩从高到低显示所有学生的所有课程的成绩以及平均成绩
思路：全部学生（一个记录集合），全部课程分数和平均分（一个记录集合），两个记录集合进行合并行转列（新的一个记录集合）
select s.sid,s.sname,max(case
b.cname when N 语文 then a.score
else null
end)as
语文 ,
max(case b.cnamewhen
N 数学 then a.score
else null
end)as
数学 ,
max(case b.cnamewhen
N 英语 then a.score
else null
end)as
英语 ,
convert(decimal(18,2),avg(a.score))as
平均成绩
from student sleft
join sc a
on s.sid=a.sidleftjoin
course b
on a.cid=b.cidgroupby
s.sid,s.sname
orderby [平均成绩]desc
–17.1 SQL 2000 静态
select a.Sid 学生编号 , a.Sname 学生姓名
,
max(case c.Cnamewhen
N 语文 then b.score
elsenullend)[语文],
max(case c.Cnamewhen
N 数学 then b.score
elsenullend)[数学],
max(case c.Cnamewhen
N 英语 then b.score
elsenullend)[英语],
cast(avg(b.score)asdecimal(18,2))平均分
from Student a
leftjoin SC bon a.Sid
= b.Sid
leftjoin Course con b.Cid
= c.Cid
groupby a.Sid , a.Sname
orderby 平均分 desc
–17.2 SQL 2000 动态
declare@sqlnvarchar(4000)
set@sql= select a.Sid +
N 学生编号 + , a.Sname +
N 学生姓名
select@sql=@sql+ ,max(case
c.Cname when N +Cname+ then b.score else null end) [+Cname+]
from (selectdistinct Cnamefrom
Course) as t
set@sql=@sql+ , cast(avg(b.score)
as decimal(18,2)) + N 平均分 +
from Student a left join SC b on a.Sid= b.Sid left join Course c on b.Cid = c.Cid
groupby a.Sid , a.Sname order by + N 平均分 +
desc
exec(@sql)
–17.3 有关 sql2005 的动静态写法参见我的文章《普通行列转换(version 2.0)》或《普通行列转换(version
3.0)》。
–18☆☆☆☆☆、查询各科成绩最高分、最低分和平均分：以如下形式显示：课程 ID，课程 name，最高分，最低分，平均分，及格率，中等率，优良率，优秀率
– 及格为 =60，中等为：70-80，优良为：80-90，优秀为：=90
思路：SC 表和 COURSE 表联合查询，每一个字段要求都可以看作是一个子查询，一个一个子查询单独做出来后，再拼接在一起。
select b.cid,b.cname,max(score)as
最高分 ,min(score)as
最低分 ,convert(decimal(5,2),avg(score))as
平均分 ,
convert(varchar,convert(decimal(5,2),convert(decimal(5,2),count(casewhen
a.score =60then 1
else null
end))/count(1)*100))+ % as
及格率 ,
convert(varchar,convert(decimal(5,2),convert(decimal(5,2),count(casewhen
a.score =70and a.score 80then 1
else null
end))/count(1)*100))+ % as
中等率 ,
convert(varchar,convert(decimal(5,2),convert(decimal(5,2),count(casewhen
a.score =80and a.score 90then 1
else null
end))/count(1)*100))+ % as
优良率 ,
convert(varchar,convert(decimal(5,2),convert(decimal(5,2),count(casewhen
a.score =90then 1
else null
end))/count(1)*100))+ % as
优秀率
from sc ainner
join course bon a.cid=b.cidgroup
by b.cid,b.cname
– 方法 1
select m.Cid[课程编号],
m.Cname[课程名称],
max(n.score)
[最高分],
min(n.score)
[最低分],
cast(avg(n.score)asdecimal(18,2))[平均分],
cast((selectcount(1)from
SC where Cid= m.Cid
and score =60)*100.0/
(selectcount(1)from
SC where Cid= m.Cid)
asdecimal(18,2))[及格率(%)],
cast((selectcount(1)from
SC where Cid= m.Cid
and score =70and score 80
)*100.0/ (selectcount(1)from
SC where Cid= m.Cid)
asdecimal(18,2))[中等率(%)],
cast((selectcount(1)from
SC where Cid= m.Cid
and score =80and score 90
)*100.0/ (selectcount(1)from
SC where Cid= m.Cid)
asdecimal(18,2))[优良率(%)],
cast((selectcount(1)from
SC where Cid= m.Cid
and score =90)*100.0/
(selectcount(1)from
SC where Cid= m.Cid)
asdecimal(18,2))[优秀率(%)]
from Course m , SC n
where m.Cid= n.Cid
groupby m.Cid , m.Cname
orderby m.Cid
– 方法 2
select m.Cid[课程编号],
m.Cname[课程名称],
(selectmax(score)from
SC where Cid= m.Cid)
[最高分],
(selectmin(score)from SCwhere Cid
= m.Cid)
[最低分],
(selectcast(avg(score)asdecimal(18,2))from
SC where Cid= m.Cid)
[平均分],
cast((selectcount(1)from
SC where Cid= m.Cid
and score =60)*100.0/
(selectcount(1)from
SC where Cid= m.Cid)
asdecimal(18,2))[及格率(%)],
cast((selectcount(1)from
SC where Cid= m.Cid
and score =70and score 80
)*100.0/ (selectcount(1)from
SC where Cid= m.Cid)
asdecimal(18,2))[中等率(%)],
cast((selectcount(1)from
SC where Cid= m.Cid
and score =80and score 90
)*100.0/ (selectcount(1)from
SC where Cid= m.Cid)
asdecimal(18,2))[优良率(%)],
cast((selectcount(1)from
SC where Cid= m.Cid
and score =90)*100.0/
(selectcount(1)from
SC where Cid= m.Cid)
asdecimal(18,2))[优秀率(%)]
from Course m
orderby m.Cid
–19、按各科成绩进行排序，并显示排名
思路：利用 over(partition by 字段名 order by
字段名)函数。
正常排序：1，2，3
select row_number()over(partitionby
cid order by cid,score
desc)as sort,*
from sc
合并重复不保留空缺：1，1，2，3
select dense_rank()over(partitionby
cid order by cid,score
desc)as sort,*
from sc
合并重复保留空缺：1，1，3
select rank() over(partitionby cid order by cid,score desc) as sort,*
from sc
–19.1 sql 2000 用子查询完成
–Score 重复时保留名次空缺
select t.* , px=
(selectcount(1)from
SC where Cid= t.Cid
and score t.score)
+1from sc torderby
t.cid , px
–Score 重复时合并名次
select t.* , px=
(selectcount(distinct score)from
SC where Cid= t.Cid
and score = t.score)
from sc t
orderby t.cid , px
–19.2sql 2005 用 rank,DENSE_RANK 完成
–Score 重复时保留名次空缺(rank 完成)
select t.* , px=
rank() over(partition
by cidorderby scoredesc)
from sc torderby t.Cid , px
–Score 重复时合并名次(DENSE_RANK 完成)
select t.* , px=
DENSE_RANK() over(partition
by cidorderby scoredesc)
from sc torderby t.Cid , px
–20、查询学生的总成绩并进行排名
思路：所有学生的总成绩（一个记录集合），再使用函数进行排序。
select rank()over(orderby
sum(a.score)desc)
as ranking,s.sid,s.sname,sum(a.score)as
总成绩 from student s
innerjoin sc a
on s.sid=a.sidgroupby
s.sid,s.sname
这个查询只能查询到有成绩的 7 名学生。
select dense_rank()over(orderby
isnull(sum(a.score),0)desc)
as ranking,s.sid,s.sname,
isnull(sum(a.score),0)as
总成绩
from student sleft
join sc a on s.sid=a.sidgroup
by s.sid,s.sname
用了 leftjoin 就可以查询到所有的 8 名学生了，包括没有成绩的 1 名学生。
–20.1 查询学生的总成绩
select m.Sid[学生编号]
,
m.Sname
[学生姓名] ,
isnull(sum(score),0)[总成绩]
from Student mleftjoin SC non
m.Sid = n.Sid
groupby m.Sid , m.Sname
orderby[总成绩]desc
–20.2 查询学生的总成绩并进行排名，sql 2000 用子查询完成，分总分重复时保留名次空缺和不保留名次空缺两种。
select t1.* , px=
(selectcount(1)from
(
select m.Sid [学生编号] ,
m.Sname
[学生姓名] ,
isnull(sum(score),0)[总成绩]
from Student m leftjoin SC non m.Sid=
n.Sid
groupby m.Sid, m.Sname
)t2where 总成绩
t1. 总成绩)+1from
(
select m.Sid [学生编号] ,
m.Sname
[学生姓名] ,
isnull(sum(score),0)[总成绩]
from Student m leftjoin SC non m.Sid=
n.Sid
groupby m.Sid, m.Sname
)t1
orderby px
select t1.* , px=
(selectcount(distinct 总成绩)from
(
select m.Sid [学生编号] ,
m.Sname
[学生姓名] ,
isnull(sum(score),0)[总成绩]
from Student m leftjoin SC non m.Sid=
n.Sid
groupby m.Sid, m.Sname
)t2where 总成绩 =
t1. 总成绩)from
(
select m.Sid [学生编号] ,
m.Sname
[学生姓名] ,
isnull(sum(score),0)[总成绩]
from Student m leftjoin SC non m.Sid=
n.Sid
groupby m.Sid, m.Sname
)t1
orderby px
–20.3 查询学生的总成绩并进行排名，sql 2005 用 rank,DENSE_RANK 完成，分总分重复时保留名次空缺和不保留名次空缺两种。
select t.* , px=
rank() over(orderby[总成绩]desc)from
(
select m.Sid [学生编号] ,
m.Sname
[学生姓名] ,
isnull(sum(score),0)[总成绩]
from Student m leftjoin SC non m.Sid=
n.Sid
groupby m.Sid, m.Sname
)t
orderby px
select t.* , px=
DENSE_RANK() over(orderby[总成绩]desc)from
(
select m.Sid [学生编号] ,
m.Sname
[学生姓名] ,
isnull(sum(score),0)[总成绩]
from Student m leftjoin SC non m.Sid=
n.Sid
groupby m.Sid, m.Sname
)t
orderby px
–21、查询不同老师所教不同课程平均分从高到低显示
思路：不同老师所教不同课程的平均分（一个记录集合），再使用函数 over(order by 字段名)
select rank()over(orderby
convert(decimal(5,2),avg(score))desc)
as ranking,c.tid,c.tname,b.cid,b.cname,
convert(decimal(5,2),avg(score))as
平均分 from sc a
innerjoin course b
on a.cid=b.cidinner
join teacher con b.tid=c.tidgroup
by c.tid,c.tname,b.cid,b.cname
select m.Tid , m.Tname ,cast(avg(o.score)asdecimal(18,2))
avg_score
from Teacher m , Course n , SCo
where m.Tid= n.Tid
and n.Cid= o.Cid
groupby m.Tid , m.Tname
orderby avg_scoredesc
–22☆、查询所有课程的成绩第 2 名到第 3 名的学生信息及该课程成绩
思路：所有课程成绩的学生及课程信息（一个记录集合），再利用函数排序（一个记录集合），选择第 2 名和第 3 名的记录。
;with abc as
(select row_number() over(partition by a.cidorder by a.score
desc)as ranking,s.sid,s.sname,a.cid,b.cname,
a.score from student sinner
join sc a on s.sid=a.sidinner
join course b on a.cid=b.cid)
select
* from abc where ranking in
(2,3)
select
* from
(select row_number() over(partition by a.cidorder by a.score
desc)as ranking,s.sid,s.sname,a.cid,b.cname,
a.score from student sinner
join sc a on s.sid=a.sidinner
join course b on a.cid=b.cid) t
where t.rankingin(2,3)
–22.1 sql 2000 用子查询完成
–Score 重复时保留名次空缺
select*from (select
t.* , px
= (selectcount(1)from
SC where Cid= t.Cid
and score t.score)
+1from sc t) mwhere px
between2and3orderby
m.cid , m.px
–Score 重复时合并名次
select*from (select
t.* , px
= (selectcount(distinct score)from
SC where Cid= t.Cid
and score = t.score)
from sc t) m
where pxbetween2and3orderby
m.cid , m.px
–22.2 sql 2005 用 rank,DENSE_RANK 完成
–Score 重复时保留名次空缺(rank 完成)
select*from (select
t.* , px
= rank() over(partitionby cid
orderby scoredesc)
from sc t) mwhere px
between2and3orderby
m.Cid , m.px
–Score 重复时合并名次(DENSE_RANK 完成)
select*from (select
t.* , px
= DENSE_RANK() over(partitionby cid
orderby scoredesc)
from sc t) mwhere px
between2and3orderby
m.Cid , m.px
–23☆☆☆、统计各科成绩各分数段人数：课程编号, 课程名称,[100-85],[85-70],[70-60],[0-60]及所占百分比
思路：SC 表和 COURSE 表联合查询（一个记录集合），然后每个字段都看做是一个子查询，最后将这些子查询拼接起来。
select b.cidas
课程编号 ,b.cnameas
课程名称 ,
count(1)as 总人数 ,
count(casewhen a.score 60then
1 else null
end) as
不及格人数 ,
convert(decimal(5,2),count(casewhen
a.score =0and a.score 60then 1
else null
end)*100/count(1))as
不及格率 % ,
count(casewhen a.score =60and
a.score 70then 1
else null
end) as
及格人数 ,
convert(decimal(5,2),count(casewhen
a.score =60and a.score 70then 1
else null
end)*100/count(1))as
及格率 % ,
count(casewhen a.score =70and
a.score 85then 1
else null
end) as
优良人数 ,
convert(decimal(5,2),count(casewhen
a.score =70and a.score 85then 1
else null
end)*100/count(1))as
优良率 % ,
count(casewhen a.score =85then
1 else null
end) as
优秀人数 ,
convert(decimal(5,2),count(casewhen
a.score =85then 1
else null
end)*100/count(1))as
优秀率 %
from sc ainner
join course bon a.cid=b.cidgroup
by b.cid,b.cname
以上方法为横向显示。
select b.cidas
课程编号 ,b.cnameas
课程名称 ,(casewhen score 60
then 0-59
when score =60
and score 70
then 60-69
when score =70
and score 85
then 70-85
else
85-100 end)
as 分数段 ,
count(1)as 人数 ,
convert(decimal(18,2),count(1)*100/(selectcount(1)from
sc where cid=b.cid))as
百分比
from sc ainner
join course bon a.cid=b.cidgroup
by all b.cid,b.cname,(casewhen
score 60 then
0-59
when score =60
and score 70
then 60-69
when score =70
and score 85
then 70-85
else
85-100 end)
orderby b.cid,b.cname, 分数段
以上方法为纵向显示，但为 0 的就不显示了。
–23.1 统计各科成绩各分数段人数：课程编号, 课程名称,[100-85],[85-70],[70-60],[0-60]
– 横向显示
select Course.Cid[课程编号]
, Cnameas[课程名称] ,
sum(casewhen score =85then1else0end)[85-100],
sum(casewhen score =70and
score 85then1else0end)[70-85],
sum(casewhen score =60and
score 70then1else0end)[60-70],
sum(casewhen score 60then1else0end)[0-60]
from sc , Course
where SC.Cid= Course.Cid
groupby Course.Cid , Course.Cname
orderby Course.Cid
– 纵向显示 1(显示存在的分数段)
select m.Cid[课程编号]
, m.Cname[课程名称] , 分数段 =
(
casewhenn.score =85then 85-100
when n.score
=70and n.score 85then 70-85
when n.score
=60and n.score 70then 60-70
else 0-60
end) ,
count(1)数量
from Course m , sc n
where m.Cid= n.Cid
groupby m.Cid , m.Cname , (
casewhenn.score =85then 85-100
when n.score
=70and n.score 85then 70-85
when n.score
=60and n.score 70then 60-70
else 0-60
end)
orderby m.Cid , m.Cname , 分数段
– 纵向显示 2(显示存在的分数段，不存在的分数段用 0 显示)
select m.Cid[课程编号]
, m.Cname[课程名称] , 分数段 =
(
casewhenn.score =85then 85-100
when n.score
=70and n.score 85then 70-85
when n.score
=60and n.score 70then 60-70
else 0-60
end) ,
count(1)数量
from Course m , sc n
where m.Cid= n.Cid
groupbyall m.Cid , m.Cname , (
casewhenn.score =85then 85-100
when n.score
=70and n.score 85then 70-85
when n.score
=60and n.score 70then 60-70
else 0-60
end)
orderby m.Cid , m.Cname , 分数段
–23.2 统计各科成绩各分数段人数：课程编号, 课程名称,[100-85],[85-70],[70-60],[60]及所占百分比
– 横向显示
select m.Cid 课程编号, m.Cname 课程名称,
(selectcount(1)from
SC where Cid= m.Cid
and score 60)[0-60],
cast((selectcount(1)from
SC where Cid= m.Cid
and score 60)*100.0/
(selectcount(1)from
SC where Cid= m.Cid)
asdecimal(18,2))[百分比(%)],
(selectcount(1)from
SC where Cid= m.Cid
and score =60and score 70)[60-70],
cast((selectcount(1)from
SC where Cid= m.Cid
and score =60and score 70)*100.0/
(selectcount(1)from
SC where Cid= m.Cid)
asdecimal(18,2))[百分比(%)],
(selectcount(1)from
SC where Cid= m.Cid
and score =70and score 85)[70-85],
cast((selectcount(1)from
SC where Cid= m.Cid
and score =70and score 85)*100.0/
(selectcount(1)from
SC where Cid= m.Cid)
asdecimal(18,2))[百分比(%)],
(selectcount(1)from
SC where Cid= m.Cid
and score =85)[85-100],
cast((selectcount(1)from
SC where Cid= m.Cid
and score =85)*100.0/
(selectcount(1)from
SC where Cid= m.Cid)
asdecimal(18,2))[百分比(%)]
from Course m
orderby m.Cid
– 纵向显示 1(显示存在的分数段)
select m.Cid[课程编号]
, m.Cname[课程名称] , 分数段 =
(
casewhenn.score =85then 85-100
when n.score
=70and n.score 85then 70-85
when n.score
=60and n.score 70then 60-70
else 0-60
end) ,
count(1)数量
,
cast(count(1)*100.0/
(selectcount(1)from
sc where Cid= m.Cid)
asdecimal(18,2))[百分比(%)]
from Course m , sc n
where m.Cid= n.Cid
groupby m.Cid , m.Cname , (
casewhenn.score =85then 85-100
when n.score
=70and n.score 85then 70-85
when n.score
=60and n.score 70then 60-70
else 0-60
end)
orderby m.Cid , m.Cname , 分数段
– 纵向显示 2(显示存在的分数段，不存在的分数段用 0 显示)
select m.Cid[课程编号]
, m.Cname[课程名称] , 分数段 =
(
casewhenn.score =85then 85-100
when n.score
=70and n.score 85then 70-85
when n.score
=60and n.score 70then 60-70
else 0-60
end) ,
count(1)数量
,
cast(count(1)*100.0/
(selectcount(1)from
sc where Cid= m.Cid)
asdecimal(18,2))[百分比(%)]
from Course m , sc n
where m.Cid= n.Cid
groupbyall m.Cid , m.Cname , (
casewhenn.score =85then 85-100
when n.score
=70and n.score 85then 70-85
when n.score
=60and n.score 70then 60-70
else 0-60
end)
orderby m.Cid , m.Cname , 分数段
–24、查询学生平均成绩及其名次
思路：所有学生的平均成绩（一个记录集合），再使用函数进行排序。
select s.sid,s.sname,row_number()over(orderby
avg(score)desc)
as ranking,convert(decimal(18,2),
avg(score))as
平均成绩 from student s
innerjoin sc a
on s.sid=a.sidgroup
by s.sid,s.sname
只显示有成绩的学生。
select s.sid,s.sname,row_number()over(orderby
avg(score)desc)
as ranking,convert(decimal(18,2),
avg(score))as
平均成绩 from student s
leftjoin sc a
on s.sid=a.sidgroup
by s.sid,s.sname
显示所有学生。
–24.1 查询学生的平均成绩并进行排名，sql 2000 用子查询完成，分平均成绩重复时保留名次空缺和不保留名次空缺两种。
select t1.* , px=
(selectcount(1)from
(
select m.Sid [学生编号] ,
m.Sname
[学生姓名] ,
isnull(cast(avg(score)asdecimal(18,2)),0)[平均成绩]
from Student m leftjoin SC non m.Sid=
n.Sid
groupby m.Sid, m.Sname
)t2where 平均成绩
t1. 平均成绩)+1from
(
select m.Sid [学生编号] ,
m.Sname
[学生姓名] ,
isnull(cast(avg(score)asdecimal(18,2)),0)[平均成绩]
from Student m leftjoin SC non m.Sid=
n.Sid
groupby m.Sid, m.Sname
)t1
orderby px
select t1.* , px=
(selectcount(distinct 平均成绩)from
(
select m.Sid [学生编号] ,
m.Sname
[学生姓名] ,
isnull(cast(avg(score)asdecimal(18,2)),0)[平均成绩]
from Student m leftjoin SC non m.Sid=
n.Sid
groupby m.Sid, m.Sname
)t2where 平均成绩 =
t1. 平均成绩)from
(
select m.Sid [学生编号] ,
m.Sname
[学生姓名] ,
isnull(cast(avg(score)asdecimal(18,2)),0)[平均成绩]
from Student m leftjoin SC non m.Sid=
n.Sid
groupby m.Sid, m.Sname
)t1
orderby px
–24.2 查询学生的平均成绩并进行排名，sql 2005 用 rank,DENSE_RANK 完成，分平均成绩重复时保留名次空缺和不保留名次空缺两种。
select t.* , px=
rank() over(orderby[平均成绩]desc)from
(
select m.Sid [学生编号] ,
m.Sname
[学生姓名] ,
isnull(cast(avg(score)asdecimal(18,2)),0)[平均成绩]
from Student m leftjoin SC non m.Sid=
n.Sid
groupby m.Sid, m.Sname
)t
orderby px
select t.* , px=
DENSE_RANK() over(orderby[平均成绩]desc)from
(
select m.Sid [学生编号] ,
m.Sname
[学生姓名] ,
isnull(cast(avg(score)asdecimal(18,2)),0)[平均成绩]
from Student m leftjoin SC non m.Sid=
n.Sid
groupby m.Sid, m.Sname
)t
orderby px
–25、查询各科成绩前三名的记录
思路：各学科成绩排序（一个记录集合），再取前 3。
select
* from
(select row_number() over(partition by a.cidorder by a.score
desc)as ranking,
s.sid,s.sname,a.score from student sinnerjoin
sc a on s.sid=a.sid) t where rankingin
(1,2,3)
–25.1 分数重复时保留名次空缺
select m.* , n.Cid , n.scorefrom
Student m, SC nwhere m.Sid
= n.Sid and n.scorein
(selecttop3 scorefrom
sc where Cid= n.Cid
orderby scoredesc)
orderby n.Cid , n.scoredesc
–25.2 分数重复时不保留名次空缺，合并名次
–sql 2000 用子查询实现
select*from (select
t.* , px
= (selectcount(distinct score)from
SC where Cid= t.Cid
and score = t.score)
from sc t) m
where pxbetween1and3orderby
m.cid , m.px
–sql 2005 用 DENSE_RANK 实现
select*from (select
t.* , px
= DENSE_RANK() over(partitionby cid
orderby scoredesc)
from sc t) mwhere px
between1and3orderby
m.Cid , m.px
–26、查询每门课程被选修的学生数
思路：每门课被选修的学生数（一个记录集合）。
select*
fromcourse a inner
join
(select cid,count(*)as
人数 from sc
group by cid) b
on a.cid=b.cid
select a.cid,a.cname,count(1)as
人数 from course a
innerjoin sc b
on a.cid=b.cidgroupby
a.cid,a.cname
select cid ,count(Sid)[学生数]from
sc groupby Cid
–27、查询出只有两门课程的全部学生的学号和姓名
select Student.Sid ,Student.Sname
from Student , SC
where Student.Sid= SC.Sid
groupby Student.Sid , Student.Sname
havingcount(SC.Cid)=2
orderby Student.Sid
–28、查询男生、女生人数
思路：
select ssex,count(1)as 人数 from
student groupby ssex
selectcount(Ssex)as 男生人数 from
Studentwhere Ssex
= N 男
selectcount(Ssex)as 女生人数 from
Studentwhere Ssex
= N 女
selectsum(casewhen
Ssex = N 男 then1else0end)[男生人数],sum(casewhen
Ssex = N 女 then1else0end)[女生人数]from
student
selectcasewhen Ssex=
N 男 then N 男生人数 else
N 女生人数 end[男女情况]
, count(1)[人数]from
studentgroupbycasewhen Ssex=
N 男 then N 男生人数 else
N 女生人数 end
–29、查询名字中含有 风 字的学生信息
select*
fromstudent where sname
like % 风 %
select*from studentwhere
sname like N % 风 %
select*from studentwherecharindex(N 风
, sname) 0
–30、查询同名同性学生名单，并统计同名人数
思路：按照姓名字段进行 GROUP BY，同时计算人数，只要大于 1，就是同姓同名。
select sname,count(1)as
人数 from student
groupby sname
having count(1) 1
select Sname[学生姓名],count(*)[人数]from
Studentgroupby Snamehavingcount(*) 1
–31、查询 1990 年出生的学生名单(注：Student 表中 Sage 列的类型是 datetime)
select*
fromstudent where
datepart(year,sage)= 1990
select*from Studentwhereyear(sage)=1990
select*from Studentwheredatediff(yy,sage, 1990-01-01)=0
select*from Studentwheredatepart(yy,sage)=1990
select*from Studentwhereconvert(varchar(4),sage,120)= 1990
–32、查询每门课程的平均成绩，结果按平均成绩降序排列，平均成绩相同时，按课程编号升序排列
思路：每门课程的平均成绩（一个记录集合），再使用函数排序，排序时根据平均成绩、课程编号。
select row_number()over(orderby
convert(decimal(18,2),avg(a.score))desc,b.cid)as
排名 ,b.cid,b.cname,convert(decimal(18,2),avg(a.score))as
平均成绩 from sc a
inner join course b
on a.cid=b.cidgroupby
b.cid,b.cname
select m.Cid , m.Cname ,cast(avg(n.score)asdecimal(18,2))
avg_score
from Course m, SC n
where m.Cid= n.Cid
groupby m.Cid , m.Cname
orderby avg_scoredesc, m.Cid
asc
–33、查询平均成绩大于等于 85 的所有学生的学号、姓名和平均成绩
select s.sid,s.sname,convert(decimal(18,2),avg(a.score))as
平均成绩 from student s
innerjoin sc a
on s.sid=a.sidgroupby
s.sid,s.snamehaving
avg(a.score) =85
select a.Sid , a.Sname ,cast(avg(b.score)asdecimal(18,2))
avg_score
from Student a , sc b
where a.Sid= b.Sid
groupby a.Sid , a.Sname
havingcast(avg(b.score)asdecimal(18,2)) =85
orderby a.Sid
–34、查询课程名称为 数学，且分数低于 60 的学生姓名和分数
select s.sid,s.sname,b.cname,a.scorefrom
student sinnerjoin sc a
on s.sid=a.sidinnerjoin
course b
on a.cid=b.cid
where b.cname= 数学 and
a.score 60
select sname , score
from Student , SC , Course
where SC.Sid= Student.Sid
and SC.Cid
= Course.Cidand Course.Cname= N 数学 and
score 60
–35、查询所有学生的课程及分数情况；
select s.sid,s.sname,b.cid,b.cname,a.score
from student sinner
join sc a on s.sid=a.sidinner
join course bon a.cid=b.cid
select Student.* , Course.Cname , SC.Cid ,SC.score
from Student, SC , Course
where Student.Sid= SC.Sid
and SC.Cid
= Course.Cid
orderby Student.Sid , SC.Cid
–36、查询任何一门课程成绩在 70 分以上的姓名、课程名称和分数；
select s.sid,s.sname,b.cid,b.cname,a.scorefrom
student sinnerjoin sc a
on s.sid=a.sidinnerjoin
course b
on a.cid=b.cid
where a.score 70
select Student.* , Course.Cname , SC.Cid ,SC.score
from Student, SC , Course
where Student.Sid= SC.Sid
and SC.Cid
= Course.Cidand SC.score
=70
orderby Student.Sid , SC.Cid
–37、查询不及格的课程
select s.sid,s.sname,b.cid,b.cname,a.scorefrom
student sinnerjoin sc a
on s.sid=a.sidinnerjoin
course b
on a.cid=b.cid
where a.score 60
select Student.* , Course.Cname , SC.Cid ,SC.score
from Student, SC , Course
where Student.Sid= SC.Sid
and SC.Cid
= Course.Cidand SC.score
60
orderby Student.Sid , SC.Cid
–38、查询课程编号为 01 且课程成绩在 80 分以上的学生的学号和姓名；
select s.sid,s.sname,b.cid,b.cname,a.scorefrom
student sinnerjoin sc a
on s.sid=a.sidinnerjoin
course b
on a.cid=b.cid
where a.score =80and b.cid= 01
select Student.* , Course.Cname , SC.Cid ,SC.score
from Student, SC , Course
where Student.Sid= SC.Sid
and SC.Cid
= Course.Cidand SC.Cid
= 01 and SC.score =80
orderby Student.Sid , SC.Cid
–39、求每门课程的学生人数
select b.cid,b.cname,count(1)as
人数 from sc a
inner join course b
on a.cid=b.cidgroupby
b.cid,b.cname
select Course.Cid , Course.Cname,count(*)[学生人数]
from Course , SC
where Course.Cid= SC.Cid
groupby Course.Cid , Course.Cname
orderby Course.Cid , Course.Cname
–40、查询选修 张三 老师所授课程的学生中，成绩最高的学生信息及其成绩
思路：上张三老师课的学生（一个记录集合）
selecttop 1
* from student s
inner join sc a
on s.sid=a.sidinnerjoin
course b
on a.cid=b.cidinnerjoin
teacher c
on b.tid=c.tidwherec.tname= 张三 order
by a.scoredesc
–40.1 当最高分只有一个时
selecttop1 Student.*
, Course.Cname , SC.Cid ,SC.score
from Student, SC , Course ,Teacher
where Student.Sid= SC.Sid
and SC.Cid
= Course.Cidand Course.Tid
= Teacher.Tid
and Teacher.Tname= N 张三
orderby SC.scoredesc
–40.2 当最高分出现多个时
select Student.* , Course.Cname , SC.Cid ,SC.score
from Student, SC , Course ,Teacher
where Student.Sid= SC.Sid
and SC.Cid
= Course.Cidand Course.Tid
= Teacher.Tid
and Teacher.Tname= N 张三 and
SC.score= (selectmax(SC.score)from
SC , Course , Teacherwhere SC.Cid
= Course.Cid
and Course.Tid= Teacher.Tid
and Teacher.Tname
= N 张三 )
–41☆☆☆☆☆、查询不同课程成绩相同的学生的学生编号、课程编号、学生成绩
思路：
– 方法 1
select m.*from SC m ,(select
Cid , score from SCgroupby Cid , scorehavingcount(1) 1)
n
where m.Cid= n.Cidand
m.score = n.score
orderby m.Cid , m.score , m.Sid
– 方法 2
select m.*from SC mwhereexists
(select1from (select Cid , scorefrom
SC groupby Cid , scorehavingcount(1) 1)
n
where m.Cid= n.Cidand
m.score = n.score)
orderby m.Cid , m.score , m.Sid
–42、查询每门课程成绩最好的前两名
思路：每门课程全部成绩（一个记录集合）。
select
* from (selectrow_number() over(partitionby cid order by score desc) as ranking,* from sc)
a whereranking in (1,2)
select t.*from sc twhere
score in (selecttop2 scorefrom
sc where Cid= T.Cid
orderby scoredesc)
orderby t.Cid , t.scoredesc
–43、统计每门课程的学生选修人数（超过 5 人的课程才统计）。要求输出课程号和选修人数，查询结果按人数降序排列，若人数相同，按课程号升序排列
select b.cid,b.cname,count(1)as
人数 from sc a
inner join course b
on a.cid=b.cidgroupby
b.cid,b.cnamehaving
count(1) 5order
by count(1)
desc,b.cid
select Course.Cid , Course.Cname,count(*)[学生人数]
from Course , SC
where Course.Cid= SC.Cid
groupby Course.Cid , Course.Cname
havingcount(*) =5
orderby[学生人数]desc
, Course.Cid
–44、检索至少选修两门课程的学生学号
select s.sid,s.sname,count(1)as
课程数 from student s
innerjoin sc a
on s.sid=a.sidgroupby
s.sid,s.snamehaving
count(1) =2
select student.Sid ,student.Sname
from student , SC
where student.Sid= SC.Sid
groupby student.Sid , student.Sname
havingcount(1) =2
orderby student.Sid
–45、查询选修了全部课程的学生信息
select s.sid,s.sname,count(1)as
课程数 from student s
innerjoin sc a
on s.sid=a.sidgroupby
s.sid,s.snamehaving
count(1) =(selectcount(1)from
course)
– 方法 1 根据数量来完成
select student.*from studentwhere
Sid in
(select Sidfrom sc
groupby Sidhavingcount(1)=
(selectcount(1)from
course))
– 方法 2 使用双重否定来完成
select t.*from student twhere
t.Sid notin
(
selectdistinctm.Sidfrom
(
select Sid , Cidfrom student , course
) m wherenotexists (select1from
sc n where n.Sid= m.Sid
and n.Cid= m.Cid)
)
– 方法 3 使用双重否定来完成
select t.*from student twherenotexists(select1from
(
selectdistinctm.Sidfrom
(
select Sid , Cidfrom student , course
) m wherenotexists (select1from
sc n where n.Sid= m.Sid
and n.Cid= m.Cid)
) kwhere k.Sid
= t.Sid
)
–46、查询各学生的年龄
select*,datediff(year,sage,getdate())as
年龄 from student
粗略算法
select*,datediff(day,sage,getdate())/365as
年龄 from student
具体算法
–46.1 只按照年份来算
select* ,datediff(yy , sage ,getdate())
[年龄]from student
–46.2 按照出生日期来算，当前月日
出生年月的月日则，年龄减一
select* ,casewhenright(convert(varchar(10),getdate(),120),5) right(convert(varchar(10),sage,120),5)thendatediff(yy
, sage ,getdate())
-1elsedatediff(yy , sage ,getdate())
end[年龄]from student
–47、查询本周过生日的学生
思路：将学生出生日期的年换成今年，然后加上具体日期，再和今天比较，如果为 0，就是本周，如果为 -1，就是下周，如果为 1，就是上周。
select*
fromstudent
wheredatediff(week,convert(varchar,datepart(yy,getdate()))+right(convert(varchar(10),sage,120),6),getdate())=0
select*from studentwheredatediff(week,datename(yy,getdate())+right(convert(varchar(10),sage,120),6),getdate())=0
–48、查询下周过生日的学生
select*
fromstudent
wheredatediff(week,convert(varchar,datepart(yy,getdate()))+right(convert(varchar(10),sage,120),6),getdate())=-1
select*from studentwheredatediff(week,datename(yy,getdate())+right(convert(varchar(10),sage,120),6),getdate())=-1
–49、查询本月过生日的学生
思路：把学生的出生日期的年换成今年，然后判断月是否在当前月。为 0 就是本月，为 1 就是上月，为 - 1 就是下月。
select*
fromstudent
wheredatediff(mm,convert(varchar,datepart(yy,getdate()))+right(convert(varchar(10),sage,120),6),getdate())=0
select*from studentwheredatediff(mm,datename(yy,getdate())+right(convert(varchar(10),sage,120),6),getdate())=0
–50、查询下月过生日的学生
select*
fromstudent
wheredatediff(mm,convert(varchar,datepart(yy,getdate()))+right(convert(varchar(10),sage,120),6),getdate())=-1
select*from studentwheredatediff(mm,datename(yy,getdate())+right(convert(varchar(10),sage,120),6),getdate())=-1
总结：
1．一种是先组合成一个总的记录集合，然后再进行 GROUP BY 或者 ORDER
BY 等其他操作；另一种是分别先对小的记录集合进行其他操作，然后再组合到一起成为最终的一个记录集合。
2．针对排序，有三种情况：
RANK()OVER()：排名 1，1，3——保留
DENSE_RANK()OVER：排名 1，1，2——不保留
ROW_NUMBEROVER()：排名 1，2，3——没有同排名的
3．有关日期的计算，一是要注意东西方对星期开始的差异，最好是使用 SET DATEFIRST 1 来人为的设定每周开始为星期一。二是要注意年、月、日三个元素的分别调整。三是要注意在调整过程中数据类型的变换。
关于“常用 SQL 语句有哪些”这篇文章就分享到这里了，希望以上内容可以对大家有一定的帮助，使各位可以学到更多知识，如果觉得文章不错，请把它分享出去让更多的人看到。
