久久精品人人爽,华人av在线,亚洲性视频网站,欧美专区一二三

常用SQL語句有哪些

135次閱讀
沒有評論

共計 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
 
丸趣
版權聲明：本站原創文章，由 丸趣 2023-07-15發表，共計33977字。
轉載說明：除特殊說明外本站除技術相關以外文章皆由網絡搜集發布，轉載請注明出處。
評論（沒有評論）
主站蜘蛛池模板： 黄浦区| 瓦房店市| 岢岚县| 鹤山市| 措美县| 长顺县| 台南市| 分宜县| 文登市| 正定县| 孟村| 逊克县| 仲巴县| 东安县| 玉屏| 叙永县| 南宫市| 五华县| 临夏市| 岳西县| 石阡县| 永年县| 漳平市| 中山市| 天峻县| 开原市| 文登市| 宿州市| 靖西县| 黎平县| 马尔康县| 凤冈县| 永德县| 垫江县| 阳西县| 名山县| 社旗县| 龙游县| 涿州市| 西安市| 永川市|