SQL常用場景50題

--1.學(xué)生表

Student(SID,Sname,Sage,Ssex) --SID 學(xué)生編號,Sname 學(xué)生姓名,Sage 出生年月,Ssex 學(xué)生性別

--2.課程表

Course(CID,Cname,TID) --CID --課程編號,Cname 課程名稱,TID 教師編號

--3.教師表

Teacher(TID,Tname) --TID 教師編號,Tname 教師姓名

--4.成績表

SC(SID,CID,score) --SID 學(xué)生編號,CID 課程編號,score 分?jǐn)?shù)

1嗦玖、查詢"01"課程比"02"課程成績高的學(xué)生的信息及課程分?jǐn)?shù)

解析：多表查詢，自連接

select a.*,SC.CID,score from Student a,SC b1,SC b2

where a.SID=sc.SID and a.SID=b1.SID and a.SID=b2.SID and b1.CID='01'and b2.CID='02' and b1.score > b2.score

上面可以改寫為,區(qū)別在于上面為豎排CID睹限，下面為橫排：

select a.*,b1.score,b2.score from Student a,SC b1,SC b2

where a.SID=b1.SID and a.SID=b2.SID and b1.CID='01'and b2.CID='02' and b1.score > b2.score

上面可以改寫為,處理不存在02課程的情況：

select a.*,b1.score,b2.score from Student a

left join SC b1 on a.SID=b1.SID and b1.CID='01'

left join SC b2 on a.SID=b1.SID and b1.CID='02'

where b1.score > isnull（b2.score,0）

2.查詢"01"課程比"02"課程成績低的學(xué)生的信息及課程分?jǐn)?shù)

解析：同上远荠，01,02 互換就行了

3.查詢平均成績大于等于60分的同學(xué)的學(xué)生編號和學(xué)生姓名和平均成績

解析：多表查詢,cast(avg(b.score) as decimal(18.2)) avg_score 代替 avg(b.score) 確定精度小數(shù)位數(shù)

select a.SID,a.Sname,b.avg(score) from Student a, SC b

where a.SID=b.SID

group by a.SID,a.Sname

having avg(b.score) >= 60

order by a.SID

4.查詢平均成績小于60分的同學(xué)的學(xué)生編號和學(xué)生姓名和平均成績

解析：同上,>= 變成 <即可;另外，如果學(xué)生表有名字剩盒，但成績表不存在成績可以用：

having isnull(cast(avg(b.score) as decimal(18,2)),0) < 60 代替having avg(b.score)

5.查詢所有同學(xué)的學(xué)生編號纵竖、學(xué)生姓名肩杈、選課總數(shù)墓造、所有課程的總成績

解析：多表查詢

①：（有成績）

select a.SID,a.Sname,count(b.CID) C_nm,sum(b.score) C_totalscore

from Student a , SC b

where a.SID=b.SID

group by a.SID,a.Sname

order by a.SID

②：（包括有成績和無成績）

select a.SID,a.Sname,count(b.CID) C_nm,sum(b.score) C_totalscore

from Student a left join SC b on a.SID=b.SID

group by a.SID,a.Sname

order by a.SID

6.查詢"李"姓老師的數(shù)量

解析： like

select count(TID) T_nm_Li from Teacher where Tname like '李%' --'李%'=CONCAT('李', '%')

或者：

select count(TID) T_nm_Li from Teacher where left(Tname,1)='李'

7.查詢學(xué)過"張三"老師授課的同學(xué)的信息

select a.* from Student a, SC b, Course c,Teacher d

where a.SID=b.SID and b.CID=C.CID and? c.TID=d.TID and d.Tname ='張三'

8.查詢沒學(xué)過"張三"老師授課的同學(xué)的信息

select * from Student where SID not in

(select a.* from Student a, SC b, Course c,Teacher d

where a.SID=b.SID and b.CID=C.CID and? c.TID=d.TID and d.Tname ='張三'

)

9.查詢學(xué)過編號為"01"并且也學(xué)過編號為"02"的課程的同學(xué)的信息

解析：先查詢學(xué)過01的堪伍，同時加入子查詢學(xué)過02的

①：--exists

select Student.* from Student , SC

where Student.SID = SC.SID and SC.CID = '01'

and exists (Select 1 from SC SC_2 where SC_2.SID = SC.SID and SC_2.CID = '02')

order by Student.SID

②：--union all

select m.* from Student m where SID in

(

? select SID from

? (

??? select distinct SID from SC where CID = '01'

??? union all

??? select distinct SID from SC where CID = '02'

? ) t group by SID having count(1) = 2 --記錄數(shù)為2

)

order by m.SID

③：簡潔

SELECT

? ? a.* FROM

? ? student a,

? ? sc b1,

? ? sc b2

WHERE a.SID = b1.SID AND a.SID = b2.SID AND b1.CID = '01' AND b2.CID = '02'

④：--

select * from Student where SID? in

(select a.SID,count(b.CID) from Student a, SC b

where a.SID=b.SID and (b.CID='01' or b.CID='02')

group by a.SID

having count(b.CID)=2

)

⑤：--

select Student.* from Student , SC

where Student.SID = SC.SID and (SC.CID = '01' or SC.CID = '02')

group by Student.SID having count(1) = 2

10.查詢學(xué)過編號為"01"但是沒有學(xué)過編號為"02"的課程的同學(xué)的信息

解析：先查詢學(xué)過01的锚烦，同時加入子查詢非學(xué)過02的

select Student.* from Student , SC

where Student.SID = SC.SID and SC.CID = '01'

and not exists (Select 1 from SC SC_2 where SC_2.SID = SC.SID and SC_2.CID = '02')

order by Student.SID

11.查詢沒有學(xué)全所有課程的同學(xué)的信息

解析：比較一下課程表中課程數(shù)和成績表中每個學(xué)生的課程數(shù)，返回學(xué)生表信息

select a.* from Student a,SC b where a.SID=b.SID

group by a.SID

having count(b.CID)<(select count(c.CID) form Course)

order by a.SID

或者：

select Student.*

from Student left join SC

on Student.SID = SC.SID

group by Student.SID , Student.Sname , Student.Sage , Student.Ssex

having count(CID) < (select count(CID) from Course)

12.查詢至少有一門課與學(xué)號為"01"的同學(xué)所學(xué)相同的同學(xué)的信息

解析：比較其他學(xué)生和01學(xué)生課程名稱帝雇、in涮俄、distinct 避免重復(fù)

select distinct a.* from Student a,SC b where a.SID<>'01'and a.SID=b.SID and b.CID in

(select CID from SC where SID='01')

13.查詢和"01"號的同學(xué)學(xué)習(xí)的課程完全相同的其他同學(xué)的信息

解析：在上面的基礎(chǔ)上，多加一個查詢記錄和01學(xué)生課程記錄數(shù)相等即可

select distinct a.* from Student a,SC b where a.SID<>'01'and a.SID=b.SID and b.CID in

(select CID from SC where SID='01')

group by a.SID,a.Sname , a.Sage , a.Ssex

having count(b.CID)=(select count(CID) from SC where SID='01')

select Student.* from Student where SID in

(select distinct SC.SID from SC where SID <> '01'

and SC.CID in (select distinct CID from SC where SID = '01')

group by SC.SID having count(1) = (select count(1) from SC where SID='01')

)

14.查詢沒學(xué)過"張三"老師講授的任一門課程的學(xué)生姓名

解析：查詢張三老師講授過的學(xué)生名字摊求，not in

select * from Student where SID not in

(select distinct a.SID from SC a,Course b, Teacher c

where a.CID=b.CID and b.TID=c.TID and c.Tname='張三')

15.查詢兩門及其以上不及格課程的同學(xué)的學(xué)號禽拔，姓名及其平均成績

select a.SID,a.Sname,avg(b.score) from Student a,SC b

where a.SID=b.SID and a.SID in (select SID from SC where score <60 group by SID count(1)>=2)

group by a.SID,a.Sname

16.檢索"01"課程分?jǐn)?shù)小于60刘离，按分?jǐn)?shù)降序排列的學(xué)生信息

select a.*,b.score from Student a, SC b where b.SID='01'and b.score <60 and a.SID=b.SID

order by b.score DESC

17.按平均成績從高到低顯示所有學(xué)生的所有課程的成績以及平均成績

解析：按照文字題意室叉，展示所有課程的成績及平均成績，可以有兩周展示方案硫惕，

第一種列分布各科成績及平均成績（推薦）茧痕；第二種行分布

select a.Sname，

sum(case when c.Cname='語文' then b.score else null end) Score_chinese,

sum(case when c.Cname='數(shù)學(xué)' then b.score else null end) Score_math

--(.......)

avg(score) avg_score

from Student a

left join SC b on a.SID=b.SID

left join Course c on b.CID=c.CID

group by a.Sname

order by avg(score) DESC

18.查詢各科成績最高分恼除、最低分和平均分：以如下形式顯示

課程ID踪旷，課程name，最高分豁辉，最低分令野，平均分，及格率徽级，中等率气破，優(yōu)良率，優(yōu)秀率

--及格為>=60餐抢，中等為：70-80现使，優(yōu)良為：80-90，優(yōu)秀為：>=90

解析：這里的*100 加不加就是結(jié)果是 72.22 與結(jié)果是 0.72 的區(qū)別最好加|| ‘%’旷痕？

select?m.CID ?課程編號 , m.Cname ?課程名稱 ,

??max(n.score) ?最高分 ,

??min(n.score) ?最低分 ,

??cast(avg(n.score) as?decimal(18,2)) ?平均分 ,

??cast((select?count(1) from?SC where?CID =?m.CID and?score >=?60)*100.0?/?(select?count(1) from?SC where?CID =?m.CID) as?decimal(18,2)) ?及格率 ,

??cast((select?count(1) from?SC where?CID =?m.CID and?score >=?70?and?score <?80?)*100.0?/?(select?count(1) from?SC where?CID =?m.CID) as?decimal(18,2)) ?中等率 ,

??cast((select?count(1) from?SC where?CID =?m.CID and?score >=?80?and?score <?90?)*100.0?/?(select?count(1) from?SC where?CID =?m.CID) as?decimal(18,2)) ?優(yōu)良率 ,

??cast((select?count(1) from?SC where?CID =?m.CID and?score >=?90)*100.0?/?(select?count(1) from?SC where?CID =?m.CID) as?decimal(18,2)) ?優(yōu)秀率

from?Course m , SC n

where?m.CID =?n.CID

group?by?m.CID , m.Cname

order?by?m.CID

和下面基本沒有啥區(qū)別碳锈，上面是直接max聚合的，需要后面寫好 group by 欺抗，下面是直接寫好最高的select的

select?m.CID ?課程編號 , m.Cname ?課程名稱 ,

??(select?max(score) from?SC where?CID =?m.CID) ?最高分 ,

??(select?min(score) from?SC where?CID =?m.CID) ?最低分 ,

??(select?cast(avg(score) as?decimal(18,2)) from?SC where?CID =?m.CID) ?平均分 ,

??cast((select?count(1) from?SC where?CID =?m.CID and?score >=?60)*100.0?/?(select?count(1) from?SC where?CID =?m.CID) as?decimal(18,2)) ?及格率,

??cast((select?count(1) from?SC where?CID =?m.CID and?score >=?70?and?score <?80?)*100.0?/?(select?count(1) from?SC where?CID =?m.CID) as?decimal(18,2)) ?中等率 ,

??cast((select?count(1) from?SC where?CID =?m.CID and?score >=?80?and?score <?90?)*100.0?/?(select?count(1) from?SC where?CID =?m.CID) as?decimal(18,2)) ?優(yōu)良率 ,

??cast((select?count(1) from?SC where?CID =?m.CID and?score >=?90)*100.0?/?(select?count(1) from?SC where?CID =?m.CID) as?decimal(18,2)) ?優(yōu)秀率

from?Course m

order?by?m.CID

19.按各科成績進(jìn)行排序售碳，并顯示排名

解析：rank() over ( partition by order by )? 保留名次空缺和合并名次

select a.*,rank() over(partition by CID order by CID DESC ) as rank? from SC a order by a.CID, rank

20.查詢學(xué)生的總成績并進(jìn)行排名

解析：查詢總成績然后排序根據(jù)需要可以分 rank 還是 dense rank

select b.*,rank() over (order by score_total desc) rank from

(select a.SID,a.Sname,isnull(sum(b.score),0)score_total from Student a

left join SC b on a.SID=b.SID

group by a.SID,a.Sname

) b

order by rank

21.查詢不同老師所教不同課程平均分從高到低顯示

select a.Tname,b.CID,avg(c.score) avg_score from Teacher a

left join Course b on a.TID=b.TID

left join SC c on b.CID=c.ICD

group by a.Tname,b.CID

order by avg_score desc

22.查詢所有課程的成績第2名到第3名的學(xué)生信息及該課程成績

解析：partition by CID, having rank between 2 to 3

select a.*,b.score,rank() over (partition by CID order by score) rank from

Student a,SC b where a.SID=b.SID and (rank between 2 to 3 )

select?*?from?

(select?t.*?, px =?rank() over(partition by?cid order?by?score desc) from?sc t) m

where?px between?2?and?3?

order?by?m.CID , m.px

23.統(tǒng)計(jì)各科成績各分?jǐn)?shù)段人數(shù)：課程編號,課程名稱, 100-85 , 85-70 , 70-60 , 0-60 及所占百分比

解析： two question 分?jǐn)?shù)段人數(shù)和占比 count(1) case when then else end?

排列：分?jǐn)?shù)段作為c列字段值，人數(shù)和占比作為d绞呈、f列字段名贸人；或者分?jǐn)?shù)段及占比作為c、d报强、e灸姊、f、g秉溉、h力惯、i碗誉、j列字段名，即分為縱向和橫向顯示兩種結(jié)果父晶。

select a.CID,b.Cname,

? ? case when score>=85 then '100-85'

? ? ? when score>=70 and score<85 then '85-70'

? ? ? when score>=60 and score<70 then '70-60'

? ? ? when score<60 then '0-60' End

as flag,

count(1) as S_nm

count(1)/(select count(1) from a where a.CID=b.CID) as S_nm%

from SC a, Course b

where a.CID=b.CID

group by a.CID,b.Cname,

case when score>=85 then '100-85'

? ? ? when score>=70 and score<85 then '85-70'

? ? ? when score>=60 and score<70 then '70-60'

? ? ? when score<60 then '0-60' End

order by a.CID,b.Cname,flag

不存在的分?jǐn)?shù)段顯示0：

select?m.CID ?課程編號 ?, m.Cname ?課程名稱 ?, 分?jǐn)?shù)段 =?(

??case?when?n.score >=?85?then?'85-100'

???????when?n.score >=?70?and?n.score <?85?then?'70-85'

???????when?n.score >=?60?and?n.score <?70?then?'60-70'

???????else?'0-60'

??end) ,

??count(1) 數(shù)量 , ?

??cast(count(1) *?100.0?/?(select?count(1) from?sc where?CID =?m.CID) as?decimal(18,2)) ?百分比

from?Course m , sc n

where?m.CID =?n.CID

group?by?all?m.CID , m.Cname , (

??case?when?n.score >=?85?then?'85-100'

???????when?n.score >=?70?and?n.score <?85?then?'70-85'

???????when?n.score >=?60?and?n.score <?70?then?'60-70'

???????else?'0-60'

??end)

order?by?m.CID , m.Cname , 分?jǐn)?shù)段

橫向顯示：

select?m.CID 課程編號, m.Cname 課程名稱,

??(select?count(1) from?SC where?CID =?m.CID and?score <?60) ?0-60 ,

??cast((select?count(1) from?SC where?CID =?m.CID and?score <?60)*100.0?/?(select?count(1) from?SC where?CID =?m.CID) as?decimal(18,2)) ?百分比 ,

??(select?count(1) from?SC where?CID =?m.CID and?score >=?60?and?score <?70) ?60-70 ,

??cast((select?count(1) from?SC where?CID =?m.CID and?score >=?60?and?score <?70)*100.0?/?(select?count(1) from?SC where?CID =?m.CID) as?decimal(18,2)) ?百分比 ,

??(select?count(1) from?SC where?CID =?m.CID and?score >=?70?and?score <?85) ?70-85 ,

??cast((select?count(1) from?SC where?CID =?m.CID and?score >=?70?and?score <?85)*100.0?/?(select?count(1) from?SC where?CID =?m.CID) as?decimal(18,2)) ?百分比 ,

??(select?count(1) from?SC where?CID =?m.CID and?score >=?85) ?85-100 ,

??cast((select?count(1) from?SC where?CID =?m.CID and?score >=?85)*100.0?/?(select?count(1) from?SC where?CID =?m.CID) as?decimal(18,2)) ?百分比

from?Course m

order?by?m.CID

24.查詢學(xué)生平均成績及其名次

解析：類似第20題

select b.*,rank() over (order by avg_total desc) rank from

(select a.SID,a.Sname,isnull(avg(b.score),0)avg_total from Student a

left join SC b on a.SID=b.SID

group by a.SID,a.Sname

) b

order by rank

25.查詢各科成績前三名的記錄

解析：先排序哮缺，然后取前三

select * from

(

select a.*,rank()over (partition by b.CID order by b.score DESC ) rank from Student a,SC b where a.SID=b.SID

)

where rank between 1 to 3

26.查詢每門課程被選修的學(xué)生數(shù)

select a.CID,a.Cname ,count(b.SID) from Course a,SC b where a.CID=b.CID group by a.CID,a.Cname

27.查詢出只有兩門課程的全部學(xué)生的學(xué)號和姓名

select a.SID,a.Sname from Student a,SC b where a.SID=b.SID

group by a.SID,a.Snmae

having count(b.CID)=2

28.查詢男生、女生人數(shù)

select case when Ssex='男' then 'male' when Ssex='女' then 'famale' END as gander ,count(1) as nm from Student

group by case when Ssex='男' then 'male' when Ssex='女' then 'famale' end

select Ssex as '性別',COUNT(1) as '人數(shù)' from Student group by Ssex

29.查詢名字中含有"風(fēng)"字的學(xué)生信息

select * from Student where Sname like '%風(fēng)%'

30.查詢同名同性學(xué)生名單甲喝，并統(tǒng)計(jì)同名人數(shù)

解析：查詢出來尝苇，記錄數(shù)大于等于2即可

select Sname,Ssex, count(1) from Student

group by Sname,Ssex

having count(1)>1

31.查詢1990年出生的學(xué)生名單

解析：限定年份即可 substr(Sage,1,4)='1990'

select * from Student where year(sage) = 1990

select * from Student where datediff(yy,sage,'1990-01-01') = 0

select * from Student where datepart(yy,sage) = 1990

select * from Student where convert(varchar(4),sage,120) = '1990'

32.查詢每門課程的平均成績，結(jié)果按平均成績降序排列埠胖，平均成績相同時糠溜，按課程編號升序排列

解析：排序

select m.CID , m.Cname , cast(avg(n.score) as decimal(18,2)) avg_score

from Course m, SC n

where m.CID = n.CID???

group by m.CID , m.Cname

order by avg_score desc, m.CID asc

33.查詢平均成績大于等于85的所有學(xué)生的學(xué)號、姓名和平均成績

解析：having

select a.SID , a.Sname , cast(avg(b.score) as decimal(18,2)) avg_score

from Student a , sc b

where a.SID = b.SID

group by a.SID , a.Sname

having cast(avg(b.score) as decimal(18,2)) >= 85

order by a.SID

34.查詢課程名稱為"數(shù)學(xué)"直撤，且分?jǐn)?shù)低于60的學(xué)生姓名和分?jǐn)?shù)

select sname , score

from Student , SC , Course

where SC.SID = Student.SID and SC.CID = Course.CID and Course.Cname ='數(shù)學(xué)' and score < 60

35.查詢所有學(xué)生的課程及分?jǐn)?shù)情況

解析：用left join比較好

select Student.Sname , Course.Cname , SC.score?

from Student, SC , Course

where Student.SID = SC.SID and SC.CID = Course.CID

order by Student.SID, SC.CID

select a.Sname,c.Cname,b.score from Student a

left join SC b on a.SID=b.SID

left join Course on b.CID=c.ICD

order by a.Sname,c.Cname

36.查詢?nèi)魏我婚T課程成績在70分以上的姓名非竿、課程名稱和分?jǐn)?shù)

select Student.Sname , Course.Cname , SC.score?

from Student, SC , Course

where Student.SID = SC.SID and SC.CID = Course.CID and score>70

order by Student.SID, SC.CID

37.查詢不及格的課程

select Student.Sname , Course.Cname , SC.score?

from Student, SC , Course

where Student.SID = SC.SID and SC.CID = Course.CID and score<60

order by Student.SID, SC.CID

38.查詢課程編號為01且課程成績在80分以上的學(xué)生的學(xué)號和姓名

select Student.SID, Student.CID ?

from Student, SC

where Student.SID = SC.SID and SC.CID = '01' and SC.score >= 80

order by Student.SID , Student.CID

39.求每門課程的學(xué)生人數(shù)

select Course.Cname , count(1) ?學(xué)生人數(shù)?

from Course , SC

where Course.CID = SC.CID

group by?Course.Cname

order by Course.Cname

40.查詢選修"張三"老師所授課程的學(xué)生中，成績最高的學(xué)生信息及其成績

解析：子查詢?yōu)閺埲蠋熓谡n學(xué)生SID

select Student.* , Course.Cname ,SC.score?

from Student, SC , Course , Teacher

where Student.SID = SC.SID and SC.CID = Course.CID and Course.TID = Teacher.TID and Teacher.Tname = '張三' and

SC.score = (select max(SC.score) from SC , Course , Teacher where SC.CID = Course.CID and Course.TID = Teacher.TID and Teacher.Tname = '張三')

當(dāng)前考慮張三老師教了多門課谋竖，取總成績最高

select Student.* , Course.Cname ,SC.score?

from Student, SC , Course , Teacher

where Student.SID = SC.SID and SC.CID = Course.CID and Course.TID = Teacher.TID and Teacher.Tname = '張三' and

Student.SID in

(select SID from

(select a.SID,sum(SC.score),rank() over(partition by SID order by sum(SC.score) desc) as rank from SC a, Course b , Teacher c

where a.CID = b.CID and b.TID =c.TID and c.Tname = '張三' and rank=1

group by a.SID)

)

41.查詢不同課程成績相同的學(xué)生的學(xué)生編號红柱、課程編號、學(xué)生成績

select a.* from SC a ,

(select CID , score from SC group by CID , score having count(1) > 1) b

where a.CID= b.CID and a.score = b.score

order by a.CID , a.score , a.SID

select m.* from SC m

where

exists

? ? ? (select 1 from

? ? ? (select CID , score from SC group by CID , score having count(1) > 1) n

? ? ? where m.CID= n.CID and m.score = n.score

? )

order by m.CID , m.score , m.SID

42.查詢每門功成績最好的前兩名

select a.* from sc a where score in

(select top 2 score from sc where CID = T.CID order by score desc)

order by a.CID , a.score desc

select a.* from SC a where a.SID in

(select SID from

? ? ? ? (select SID,rank() over(partition by CID order by score desc) as rank where rank=2)

)

order by a.CID , a.score desc

43.統(tǒng)計(jì)每門課程的學(xué)生選修人數(shù)（超過5人的課程才統(tǒng)計(jì)）蓖乘。要求輸出課程號和選修人數(shù)锤悄，查詢結(jié)果按人數(shù)降序排列，若人數(shù)相同嘉抒，按課程號升序排列

select Course.CID , Course.Cname , count(*) ?學(xué)生人數(shù)?

from Course , SC

where Course.CID = SC.CID

group by? Course.CID , Course.Cname

having count(*) >= 5

order by ?學(xué)生人數(shù)??desc , Course.CID

44.檢索至少選修兩門課程的學(xué)生學(xué)號

select SID from SC group by SID

having count(1) >= 2

order by SID

45.查詢選修了全部課程的學(xué)生信息

解析：課程表數(shù)量和成績表中數(shù)量即可

select student.* from student where SID in

(select SID from sc group by SID having count(1) = (select count(1) from course))

46.查詢各學(xué)生的年齡

select * , datediff(yy , sage , getdate())? 年齡? from student

select * ,

case when right(convert(varchar(10),getdate(),120),5) < right(convert(varchar(10),sage,120),5) then datediff(yy , sage , getdate()) - 1

else datediff(yy , sage , getdate()) end ? --為什么不是102而是120 零聚，也可以用substr

年齡??from student

47.查詢本周過生日的學(xué)生

select * from student where datediff(week,datename(yy,getdate()) - right(convert(varchar(10),sage,120),6),getdate()) = 0

select * from student where ((select WEEKOFYEAR(now()))- (select WEEKOFYEAR(sage))) =0

48.查詢下周過生日的學(xué)生

select * from student where datediff(week,datename(yy,getdate()) - right(convert(varchar(10),sage,120),6),getdate()) = -1

select * from student where ((select WEEKOFYEAR(now()))- (select WEEKOFYEAR(sage))) = -1

49.查詢本月過生日的學(xué)生

select * from student where datediff(mm,datename(yy,getdate()) - right(convert(varchar(10),sage,120),6),getdate()) = 0

select * from student where ((select month(now()))- (select month(sage))) = 0

50.查詢下月過生日的學(xué)生

select * from student where datediff(mm,datename(yy,getdate()) - right(convert(varchar(10),sage,120),6),getdate()) = -1

select * from student where ((select month(now()))- (select month(sage))) = -1

?著作權(quán)歸作者所有,轉(zhuǎn)載或內(nèi)容合作請聯(lián)系作者

人面猴
序言：七十年代末，一起剝皮案震驚了整個濱河市众眨，隨后出現(xiàn)的幾起案子握牧，更是在濱河造成了極大的恐慌，老刑警劉巖娩梨，帶你破解...
沈念sama閱讀 210,978評論 6贊 490
死咒
序言：濱河連續(xù)發(fā)生了三起死亡事件沿腰，死亡現(xiàn)場離奇詭異，居然都是意外死亡狈定，警方通過查閱死者的電腦和手機(jī)颂龙，發(fā)現(xiàn)死者居然都...
沈念sama閱讀 89,954評論 2贊 384
救了他兩次的神仙讓他今天三更去死
文/潘曉璐我一進(jìn)店門，熙熙樓的掌柜王于貴愁眉苦臉地迎上來纽什，“玉大人措嵌，你說我怎么就攤上這事÷郑” “怎么了企巢？”我有些...
開封第一講書人閱讀 156,623評論 0贊 345
道士緝兇錄：失蹤的賣姜人
文/不壞的土叔我叫張陵，是天一觀的道長让蕾。經(jīng)常有香客問我浪规，道長或听，這世上最難降的妖魔是什么？我笑而不...
開封第一講書人閱讀 56,324評論 1贊 282
?港島之戀（遺憾婚禮）
正文為了忘掉前任笋婿，我火速辦了婚禮誉裆，結(jié)果婚禮上，老公的妹妹穿的比我還像新娘缸濒。我一直安慰自己足丢，他們只是感情好，可當(dāng)我...
茶點(diǎn)故事閱讀 65,390評論 5贊 384
惡毒庶女頂嫁案：這布局不是一般人想出來的
文/花漫我一把揭開白布庇配。她就那樣靜靜地躺著斩跌，像睡著了一般。火紅的嫁衣襯著肌膚如雪讨永。梳的紋絲不亂的頭發(fā)上滔驶，一...
開封第一講書人閱讀 49,741評論 1贊 289
城市分裂傳說
那天，我揣著相機(jī)與錄音卿闹，去河邊找鬼。笑死萝快，一個胖子當(dāng)著我的面吹牛锻霎，可吹牛的內(nèi)容都是我干的。我是一名探鬼主播揪漩，決...
沈念sama閱讀 38,892評論 3贊 405
雙鴛鴦連環(huán)套：你想象不到人心有多黑
文/蒼蘭香墨我猛地睜開眼旋恼，長吁一口氣：“原來是場噩夢啊……” “哼！你這毒婦竟也來了奄容？” 一聲冷哼從身側(cè)響起冰更，我...
開封第一講書人閱讀 37,655評論 0贊 266
萬榮殺人案實(shí)錄
序言：老撾萬榮一對情侶失蹤，失蹤者是張志新（化名）和其女友劉穎昂勒，沒想到半個月后蜀细，有當(dāng)?shù)厝嗽跇淞掷锇l(fā)現(xiàn)了一具尸體，經(jīng)...
沈念sama閱讀 44,104評論 1贊 303
?護(hù)林員之死
正文獨(dú)居荒郊野嶺守林人離奇死亡戈盈，尸身上長有42處帶血的膿包…… 初始之章·張勛以下內(nèi)容為張勛視角年9月15日...
茶點(diǎn)故事閱讀 36,451評論 2贊 325
?白月光啟示錄
正文我和宋清朗相戀三年奠衔，在試婚紗的時候發(fā)現(xiàn)自己被綠了。大學(xué)時的朋友給我發(fā)了我未婚夫和他白月光在一起吃飯的照片塘娶。...
茶點(diǎn)故事閱讀 38,569評論 1贊 340
活死人
序言：一個原本活蹦亂跳的男人離奇死亡归斤，死狀恐怖，靈堂內(nèi)的尸體忽然破棺而出刁岸，到底是詐尸還是另有隱情脏里，我是刑警寧澤，帶...
沈念sama閱讀 34,254評論 4贊 328
?日本核電站爆炸內(nèi)幕
正文年R本政府宣布虹曙，位于F島的核電站迫横，受9級特大地震影響鸦难，放射性物質(zhì)發(fā)生泄漏。R本人自食惡果不足惜员淫，卻給世界環(huán)境...
茶點(diǎn)故事閱讀 39,834評論 3贊 312
男人毒藥：我在死后第九天來索命
文/蒙蒙一合蔽、第九天我趴在偏房一處隱蔽的房頂上張望。院中可真熱鬧介返，春花似錦拴事、人聲如沸。這莊子的主人今日做“春日...
開封第一講書人閱讀 30,725評論 0贊 21
一樁弒父案刃宵，背后竟有這般陰謀
文/蒼蘭香墨我抬頭看了看天上的太陽。三九已至徘公，卻和暖如春牲证，著一層夾襖步出監(jiān)牢的瞬間，已是汗流浹背关面。一陣腳步聲響...
開封第一講書人閱讀 31,950評論 1贊 264
情欲美人皮
我被黑心中介騙來泰國打工坦袍，沒想到剛下飛機(jī)就差點(diǎn)兒被人妖公主榨干…… 1. 我叫王不留，地道東北人等太。一個月前我還...
沈念sama閱讀 46,260評論 2贊 360
代替公主和親
正文我出身青樓捂齐，卻偏偏與公主長得像，于是被迫代替她去往敵國和親缩抡。傳聞我的和親對象是個殘疾皇子奠宜，可洞房花燭夜當(dāng)晚...
茶點(diǎn)故事閱讀 43,446評論 2贊 348

SQL常用場景50題

推薦閱讀更多精彩內(nèi)容