常用SQL语句有哪些

59次阅读
没有评论

共计 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 语句有哪些”这篇文章就分享到这里了,希望以上内容可以对大家有一定的帮助,使各位可以学到更多知识,如果觉得文章不错,请把它分享出去让更多的人看到。

正文完
 
丸趣
版权声明:本站原创文章,由 丸趣 2023-07-15发表,共计33977字。
转载说明:除特殊说明外本站除技术相关以外文章皆由网络搜集发布,转载请注明出处。
评论(没有评论)