常见的SQL高级语句

问题及描述：

–1。学生表

Student（SID，Sname，Sage，Ssex）–SID学生编号，Sname学生姓名，Sage出生年月，Ssex学生性别

–2。课程表

课程（CID，Cname，TID）–CID-课程编号，Cname课程名称，TID教师编号

–3。教师表

Teacher（TID，Tname）–TID教师编号，Tname教师姓名

–4。成绩表

SC（SID，CID，分数）–SID学生编号，CID课程编号，分数

• /

• 创建测试数据

创建表Student（SID varchar（10），Sname nvarchar（10），Sage datetime，Ssex nvarchar（10））;

插入到学生值中（‘01’，‘赵雷’，‘1990-01-01’，‘男’）;

插入到学生值中（‘02’，‘钱电’，‘1990-12-21’，‘男’）;

插入到学生值中（‘03’，‘孙风’，‘1990-05-20’，‘男’）;

插入到学生值中（‘04’，‘李云’，‘1990-08-06’，‘男’）;

插入到学生值中（‘05’，‘周梅’，‘1991-12-01’，‘女’）;

插入到学生值中（‘06’，‘吴兰’，‘1992-03-01’，‘女’）;

插入到学生值中（‘07’，‘郑竹’，‘1989-07-01’，‘女’）;

插入到学生值中（‘08’，‘王菊’，‘1990-01-20’，‘女’）;

创建表Course（CID varchar（10），Cname nvarchar（10），TID varchar（10））;

插入课程值（“ 01”，“语文”，“ 02”）；

插入课程值（‘02’，‘数学’，‘01’）;

插入课程值（“ 03”，“英语”，“ 03”）；

创建表Teacher（TID varchar（10），Tname nvarchar（10））;

插入教师值（‘01’，‘张三’）;

插入教师值（“ 02”，“李四”）；

插入教师值（“ 03”，“王五”）；

创建表SC（SID varchar（10），CID varchar（10），分数十进制（18,1））;

插入SC值（‘01’，‘01’，80）;

插入SC值（‘01’，‘02’，90）;

插入SC值（‘01’，‘03’，99）;

插入SC值（‘02’，‘01’，70）;

插入SC值（‘02’，‘02’，60）;

插入SC值（‘02’，‘03’，80）;

插入SC值（‘03’，‘01’，80）;

插入SC值（‘03’，‘02’，80）;

插入SC值（‘03’，‘03’，80）;

插入SC值（‘04’，‘01’，50）;

插入SC值（‘04’，‘02’，30）;

插入SC值（‘04’，‘03’，20）;

插入SC值（‘05’，‘01’，76）;

插入SC值（‘05’，‘02’，87）;

插入SC值（‘06’，‘01’，31）;

插入SC值（‘06’，‘03’，34）;

插入SC值（‘07’，‘02’，89）;

插入SC值（‘07’，‘03’，98）;

–1，查询 “01” 课程比 “02” 课程成绩高的学生的信息及课程分数

–1.1，查询同时存在 “01” 课程和 “02” 课程的情况

从学生a，SC b，SC c中选择a。*，b.score课程01的分数，c.score课程02的分数

其中a.SID = b.SID和a.SID = c.SID和b.CID ='01’和c.CID ='02’且b.score> c.score

–1.2，查询同时存在 “01” 课程和 “02” 课程的情况和存在 “01” 课程但可能不存在 “02” 课程的情况（不存在时显示为空）（以下存在相同内容时不再解释）

从学生a中选择a。*，b.score课程01的分数，c.score课程02的分数

在a.SID = b.SID和b.CID ='01’上向左加入SC b

在a.SID = c.SID和c.CID ='02’上左加入SC c

其中b.score> isull（c.score，0）

方法1.1 sql解析（基于mysql数据库，方法1.2不是mysql数据库，mysql数据库里面没有isull函数）查询流程如下：

–2，查询 “01” 课程比 “02” 课程成绩低的学生的信息及课程分数

–2.1，查询同时存在 “01” 课程和 “02” 课程的情况

从学生a，SC b，SC c中选择a。*，b.score课程01的分数，c.score课程02的分数

其中a.SID = b.SID和a.SID = c.SID和b.CID ='01’和c.CID ='02’且b.score <c.score

–2.2，查询同时存在 “01” 课程和 “02” 课程的情况和不存在 “01” 课程但存在 “02” 课程的情况

从学生a中选择a。*，b.score课程01的分数，c.score课程02的分数

在a.SID = b.SID和b.CID ='01’上向左加入SC b

在a.SID = c.SID和c.CID ='02’上左加入SC c

其中notull（b.score，0）<c.score

–3，查询平均成绩大于等于60分的同学的学生编号和学生姓名和平均成绩

选择a.SID，a.Sname，cast（avg（b.score）作为十进制（18,2））avg_score

来自学生a，sc b

其中a.SID = b.SID

按a.SID分组

具有cast（avg（b.score）作为十进制（18,2））> = 60

通过a.SID订购

–4，查询平均成绩小于60分的同学的学生编号和学生姓名和平均成绩

–4.1，查询在SC表存在成绩的学生信息的SQL语句。

选择a.SID，a.Sname，cast（avg（b.score）作为十进制（18,2））avg_score

来自学生a，sc b

其中a.SID = b.SID

按a.SID分组

具有cast（avg（b.score）作为十进制（18,2））<60

通过a.SID订购

–4.2，查询在SC表中不存在成绩的学生信息的SQL语句。

选择a.SID，a.Sname，isnull（cast（avg（b.score）作为十进制（18,2）），0）avg_score

来自学生a左加入sc b

在a.SID = b.SID上

按a.SID分组

具有notull（cast（avg（b.score）as decimal（18,2）），0）<60

通过a.SID订购

–5，查询所有同学的学生编号，学生姓名，选课总数，所有课程的总成绩

–5.1，查询所有有成绩的SQL。

选择a.SID学生编号，a.Sname学生姓名，count（b.CID）选课总数，sum（分数）所有课程的总成绩

来自学生a，SC b

其中a.SID = b.SID

按a.SID，a.Sname分组

通过a.SID订购

–5.2，查询所有（包括有成绩和无成绩）的SQL。

选择a.SID学生编号，a.Sname学生姓名，count（b.CID）选课总数，sum（分数）所有课程的总成绩

来自学生a左加入SC b

在a.SID = b.SID上

按a.SID，a.Sname分组

通过a.SID订购

–6，查询 “李” 姓老师的数量

• 方法1

从教师那里选择count（Tname）李姓老师的数量，其中Tname喜欢’李％’

• 方法2

从教师那里选择count（Tname）李姓老师的数量，其中left（Tname，1）=‘李’

–7，查询学过 “张三” 老师授课的同学的信息

从学生，SC，课程，老师中选择不同的学生。*

其中Student.SID = SC.SID和SC.CID = Course.CID和Course.TID = Teacher.TID和Teacher.Tname =‘张三’

按Student.SID订购

–8，查询没学过 “张三” 老师授课的同学的信息

从不在其中的SID的学生m中选择m。*（从SC，Course，Teacher中选择不同的SC.SID，其中SC.CID = Course.CID和Course.TID = Teacher.TID和Teacher.Tname =‘张三’）顺序通过m.SID

–9，查询学过编号为 “01”，并且也学过编号为 “02” 的课程的同学的信息

• 方法1

从Student中选择Student。*，其中Student.SID = SC.SID和SC.CID ='01’并且存在（从SC SC_2中选择1，其中SC_2.SID = SC.SID和SC_2.CID =‘02’）顺序通过Student.SID

• 方法2

从Student中选择Student。*，其中Student.SID = SC.SID和SC.CID ='02’并存在（从SC SC_2中选择1，其中SC_2.SID = SC.SID和SC_2.CID =‘01’）顺序通过Student.SID

• 方法3

从学生m中选择m。*，其中SID在

（

从中选择SID

（

从SC中选择不同的SID，其中CID =‘01’

全部合并

从SC中选择不同的SID，其中CID =‘02’

）t按count（1）= 2的SID分组

）

通过m.SID订购

–10，查询学过编号为 “01”，但是没有学过编号为 “02” 的课程的同学的信息

• 方法1

从Student中选择Student。*，其中Student.SID = SC.SID和SC.CID ='01’且不存在（从SC SC_2中选择1，其中SC_2.SID = SC.SID和SC_2.CID =‘02’）按Student.SID订购

• 方法2

从Student，SC中选择Student。*，其中Student.SID = SC.SID和SC.CID =‘01’，并且不在Student.SID中（从SC SC_2中选择SC_2.SID，其中SC_2.SID = SC.SID和SC_2.CID =‘02’）按Student.SID订购

–11，查询没有学全所有课程的同学的信息

–11.1，

选择学生。*

来自SC的学生

其中Student.SID = SC.SID

按Student.SID，Student.Sname，Student.Sage，Student.Ssex分组，其count（CID）<（从课程中选择count（CID））

–11.2

选择学生。*

从学生离开加入SC

在Student.SID = SC.SID上

按Student.SID，Student.Sname，Student.Sage，Student.Ssex分组，其count（CID）<（从课程中选择count（CID））

–12，查询至少有一门课与学号为 “01” 的同学所学相同的同学的信息

从Student，SC中选择不同的Student。*，其中Student.SID = SC.SID和SC.CID（从SC中选择CID，其中SID =‘01’）和Student.SID <>‘01’

–13，查询和 “01” 号的同学学习的课程完全相同的其他同学的信息

从学生中选择SID。*

（从SID <>'01’的SC中选择不同的SC.SID，并在其中从SID ='01’的SC中选择不同的CID）

由具有count（1）=（从SC中选择count（1）的SID = '01’的SC.SID分组））

–14，查询没学过 “张三” 老师讲授的任一门课程的学生姓名

从学生中选择学生。*其中学生.SID不存在

（从sc，course，Teacher中选择不同的sc.SID，其中sc.CID = course.CID和course.TID = Teacher.TID and Teacher.tname =‘张三’）

按学生订购

–15，查询两门及其以上不及格课程的同学的学号，姓名及其平均成绩

从student，sc中选择student.SID，student.sname，cast（avg（score）as decimal（18,2））avg_score

其中student.SID = SC.SID和student.SID（从SC中选择SID，其中得分<60组，按具有count（1）> = 2的SID）

按student.SID分组，student.sname

–16，检索 “01” 课程分数小于60，按分数降序排列的学生信息

选择学生。*，sc.CID，学生的sc.score，sc

其中student.SID = SC.SID，sc.score <60，sc.CID =‘01’

由sc.score desc订购

–17，按平均成绩从高到低显示所有学生的所有课程的成绩以及平均成绩

–17.1 SQL 2000静态

选择a.SID学生编号，a.Sname学生姓名，

max（case’C.Cname’当’语文’时b.score else null结尾）语文，

max（case c.Cname when’math’then b.score else null end）数学，

max（case’C.Cname when’English’then then b.score else null end）汉语

cast（avg（b.score）as decimal（18,2））平均分

来自学生a

在a.SID = b.SID上左加入SC b

在b.CID = c.CID上左加入课程c

按a.SID分组

按平均分desc排序

–17.2 SQL 2000动态

声明@sql nvarchar（4000）

set @sql =‘选择a.SID’+‘学生编号’+’，a.Sname’+‘学生姓名’

选择@sql = @sql +’，max（当’’’+ Cname +’’‘时为c.Cname，然后b.score否则为null结束）’+ Cname +’’

来自（从课程中选择不同的Cname）作为t

设置@sql = @sql +’，cast（avg（b.score）为十进制（18,2））’+‘平均分’+'来自学生a左连接SC b在a.SID = b.SID左连接b.CID上的课程c = c.CID

按a.SID分组，按。+名称排序，按’+‘平均分’+‘desc’

EXEC（@sql）

–18，查询各科成绩最高分，最低分和平均分：以如下形式显示：课程ID，课程名称，最高分，最低分，平均分，及格率，中等率，优良率，优秀率

• 及格为> = 60，中等为：70-80，优良为：80-90，优秀为：> = 90

• 方法1

选择m.CID课程编号，m.Cname课程名称，

max（n.score）最高分，

min（n.score）最低分，

铸（AVG（n.score）为十进制（18，2））平均分，

铸（（SELECT COUNT（1）从SC，其中CID = m.CID和得分> = 60）* 100.0 /（SELECT COUNT（1）从SC，其中CID = m.CID）为十进制（18，2））及格率，

铸（（SELECT COUNT（1从SC），其中CID = m.CID和得分> = 70和评分< 80）* 100.0 /（SELECT COUNT（1）从SC，其中CID = m.CID）为十进制（18，2））中等率，

铸（（SELECT COUNT（1从SC），其中CID = m.CID和得分> = 80和评分< 90）* 100.0 /（SELECT COUNT（1）从SC，其中CID = m.CID）为十进制（18，2））优良率，

铸（（SELECT COUNT（1）从SC，其中CID = m.CID和得分> = 90）* 100.0 /（SELECT COUNT（1）从SC，其中CID = m.CID）为十进制（18，2））优秀率

来自课程m

其中m.CID = n.CID

按m.CID分组

按m.CID订购

• 方法2

选择m.CID课程编号，m.Cname课程名称，

（从SC中选择max（score），其中CID = m.CID）最高分，

（从SC中选择min（分数），其中CID = m.CID）最低分，

（选择流延（AVG（评分）为十进制（18，2从SC））其中CID = m.CID）平均分，

铸（（SELECT COUNT（1）从SC，其中CID = m.CID和得分> = 60）* 100.0 /（SELECT COUNT（1）从SC，其中CID = m.CID）为十进制（18，2））及格率，

铸（（SELECT COUNT（1从SC），其中CID = m.CID和得分> = 70和评分< 80）* 100.0 /（SELECT COUNT（1）从SC，其中CID = m.CID）为十进制（18，2））中等率，

铸（（SELECT COUNT（1从SC），其中CID = m.CID和得分> = 80和评分< 90）* 100.0 /（SELECT COUNT（1）从SC，其中CID = m.CID）为十进制（18，2））优良率，

铸（（SELECT COUNT（1）从SC，其中CID = m.CID和得分> = 90）* 100.0 /（SELECT COUNT（1）从SC，其中CID = m.CID）为十进制（18，2））优秀率

来自课程m

按m.CID订购

–19，按各科成绩进行排序，并显示排名

–19.1 sql 2000用子查询完成

–score重复时保留名次空缺

选择t。*，px =（从SC中选择count（1），其中CID = t.CID且得分> t.score）+ 1从sc t顺序按t.cid，px

–score重复时合并名次

选择t。*，px =（从SC选择count（distinct分数），其中CID = t.CID且分数> = t.score）从t顺序按t.cid，px

–19.2 sql 2005用等级，DENSE_RANK完成

–score重复时保留名次空缺（秩完成）

选择t。*，px = rank（）over（按t.CID按ts从ct顺序划分）

–score重复时合并名次（DENSE_RANK完成）

选择t。*，px = DENSE_RANK（）over（按t.CID按px的ct顺序对cid进行划分）

–20，查询学生的总成绩并进行排名

–20.1查询学生的总成绩

选择m.SID学生编号，

m.Sname学生姓名，

notull（sum（score），0）总成绩

从学生m左加入m.SID = n.SID上的SC n

按m.SID分组，m.Sname

由总成绩desc订购

–20.2查询学生的总成绩并进行排名，sql 2000用子查询完成，分总分重复时保留名次空缺和不保留名次空缺两种。

选择t1。*，px =（从中选择count（1）

（

选择m.SID学生编号，

m.Sname学生姓名，

notull（sum（score），0）总成绩

从学生m左加入m.SID = n.SID上的SC n

按m.SID分组，m.Sname

）T 2，其中总成绩> T1。总成绩）+ 1从

（

选择m.SID学生编号，

m.Sname学生姓名，

notull（sum（score），0）总成绩

从学生m左加入m.SID = n.SID上的SC n

按m.SID分组，m.Sname

）t1

按px排序

选择t1。*，px =（从中选择count（distinct总成绩）

（

选择m.SID学生编号，

m.Sname学生姓名，

notull（sum（score），0）总成绩

从学生m左加入m.SID = n.SID上的SC n

按m.SID分组，m.Sname

）t2，其中总成绩> = t1。总成绩）

（

选择m.SID学生编号，

m.Sname学生姓名，

notull（sum（score），0）总成绩

从学生m左加入m.SID = n.SID上的SC n

按m.SID分组，m.Sname

）t1

按px排序

–20.3查询学生的总成绩并进行排名，sql 2005用等级，DENSE_RANK完成，分总分重复时保留名次空缺和不保留名次空缺两种。

选择t。*，px = rank（）over（由总成绩desc排序）

（

选择m.SID学生编号，

m.Sname学生姓名，

notull（sum（score），0）总成绩

从学生m左加入m.SID = n.SID上的SC n

按m.SID分组，m.Sname

）吨

按px排序

选择t。*，px = DENSE_RANK（）over（由总成绩desc排序）

（

选择m.SID学生编号，

m.Sname学生姓名，

notull（sum（score），0）总成绩

从学生m左加入m.SID = n.SID上的SC n

按m.SID分组，m.Sname

）吨

按px排序

• 21，查询不同老师所教不同课程平均分从高到低显示

选择m.TID，m.Tname，流延（AVG（o.score）为十进制（18，2））avg_score

来自m老师，课程n，SC o

其中m.TID = n.TID和n.CID = o.CID

按m.TID分组

按avg_score desc排序

–22，查询所有课程的成绩第2名到第3名的学生信息及该课程成绩

–22.1 sql 2000用子查询完成

–score重复时保留名次空缺

选择* from（选择t。*，px =（从SC 选择count（1），其中CID = t.CID且得分> t.score）+ 1从sc t）m其中px在2到3之间按m.cid排序，m.px

–score重复时合并名次

select * from（从select t。*，px =（从SC选择count（distinct score），其中CID = t.CID并且得分> = t.score）从sc t）m其中px在2和3之间为m.cid ，m.px

–22.2 sql 2005用等级，DENSE_RANK完成

–score重复时保留名次空缺（秩完成）

select * from（select t。*，px = rank（）over（从ct顺序按得分desc划分）从sc t）m其中px在2和3之间的顺序是m.CID，m.px

–score重复时合并名次（DENSE_RANK完成）

select * from（select t。*，px = DENSE_RANK（）over（从sc t）按cid顺序按得分desc划分）m其中px在2到3之间按m.CID排序，m.px

–23，统计各科成绩各分数段人数：课程编号，课程名称，100-85，85-70，70-60，0-60及所占百分比

–23.1统计各科成绩各分数段人数：课程编号，课程名称，100-85，85-70，70-60，0-60

• 横向显示

选择Course.CID课程编号，Cname作为课程名称，

总和（情况下，当得分> = 85然后1其他0端）85-100，

总和（情况下，当得分> = 70和评分< 85然后1个其他0端）70-85，

sum（分数> = 60且分数< 70则1否则0结束的情况）60-70，

总和（情况下，当得分< 60然后1其他0端）0-60

从sc，课程

其中SC.CID = Course.CID

按Course.CID分组，Course.Cname

按Course.CID订购

• 纵向显示1（显示存在的分数段）

选择m.CID课程编号，m.Cname课程名称，分数段=（

n.score> = 85则为’85 -100’

当n.score> = 70并且n.score < 85时 ‘70 -85’

当n.score> = 60且n.score < 70时为 ‘60 -70’

否则为“ 0-60”

结束） ，

count（1）数量

从课程m到

其中m.CID = n.CID

按m.CID分组，m.Cname，（

n.score> = 85则为’85 -100’

当n.score> = 70并且n.score < 85时 ‘70 -85’

当n.score> = 60且n.score < 70时为 ‘60 -70’

否则为“ 0-60”

结束）

按m.CID，m.Cname，分数段排序

• 纵向显示2（显示存在的分数段，不存在的分数段用0显示）

选择m.CID课程编号，m.Cname课程名称，分数段=（

n.score> = 85则为’85 -100’

当n.score> = 70并且n.score < 85时 ‘70 -85’

当n.score> = 60且n.score < 70时为 ‘60 -70’

否则为“ 0-60”

结束） ，

count（1）数量

从课程m到

其中m.CID = n.CID

按所有m.CID，m.Cname，（

n.score> = 85则为’85 -100’

当n.score> = 70并且n.score < 85时 ‘70 -85’

当n.score> = 60且n.score < 70时为 ‘60 -70’

否则为“ 0-60”

结束）

按m.CID，m.Cname，分数段排序

–23.2统计各科成绩各分数段人数：课程编号，课程名称，100-85，85-70，70-60，<60及所占百分比

• 横向显示

选择m.CID课程编号，m.Cname课程名称，

（从SC中选择count（1），其中CID = m.CID且得分< 60）0-60，

铸（（SELECT COUNT（1）从SC，其中CID = m.CID和得分< 60）* 100.0 /（SELECT COUNT（1从（SC其中CID = m.CID）为十进制）18，2））百分比，

（从SC中选择count（1），其中CID = m.CID且得分> = 60且得分< 70）60-70，

铸（（SELECT COUNT（1从SC），其中CID = m.CID和得分> = 60和评分< 70）* 100.0 /（SELECT COUNT（1）从SC，其中CID = m.CID）为十进制（18，2））百分比，

（从SC中选择count（1），其中CID = m.CID且得分> = 70且得分< 85）70-85，

铸（（SELECT COUNT（1从SC），其中CID = m.CID和得分> = 70和评分< 85）* 100.0 /（SELECT COUNT（1）从SC，其中CID = m.CID）为十进制（18，2））百分比，

（从SC中选择count（1），其中CID = m.CID且得分> = 85）85-100，

铸（（SELECT COUNT（1）从SC，其中CID = m.CID和得分> = 85）* 100.0 /（SELECT COUNT（1）从SC，其中CID = m.CID）为十进制（18，2））百分比

来自课程m

按m.CID订购

• 纵向显示1（显示存在的分数段）

选择m.CID课程编号，m.Cname课程名称，分数段=（

n.score> = 85则为’85 -100’

当n.score> = 70并且n.score < 85时 ‘70 -85’

当n.score> = 60且n.score < 70时为 ‘60 -70’

否则为“ 0-60”

结束） ，

count（1）数量，

铸（计数（1）* 100.0 /（SELECT COUNT（1）选自Sc，其中CID = m.CID）为十进制（18，2））百分比

从课程m到

其中m.CID = n.CID

按m.CID分组，m.Cname，（

n.score> = 85则为’85 -100’

当n.score> = 70并且n.score < 85时 ‘70 -85’

当n.score> = 60且n.score < 70时为 ‘60 -70’

否则为“ 0-60”

结束）

按m.CID，m.Cname，分数段排序

• 纵向显示2（显示存在的分数段，不存在的分数段用0显示）

选择m.CID课程编号，m.Cname课程名称，分数段=（

n.score> = 85则为’85 -100’

当n.score> = 70并且n.score < 85时 ‘70 -85’

当n.score> = 60且n.score < 70时为 ‘60 -70’

否则为“ 0-60”

结束） ，

count（1）数量，

铸（计数（1）* 100.0 /（SELECT COUNT（1）选自Sc，其中CID = m.CID）为十进制（18，2））百分比

从课程m到

其中m.CID = n.CID

按所有m.CID，m.Cname，（

n.score> = 85则为’85 -100’

当n.score> = 70并且n.score < 85时 ‘70 -85’

当n.score> = 60且n.score < 70时为 ‘60 -70’

否则为“ 0-60”

结束）

按m.CID，m.Cname，分数段排序

–24，查询学生平均成绩及其名次

–24.1查询学生的平均成绩并进行排名，sql 2000用子查询完成，分平均成绩重复时保留名次空缺和不保留名次空缺两种。

选择t1。*，px =（从中选择count（1）

（

选择m.SID学生编号，

m.Sname学生姓名，

notull（cast（avg（score）as decimal（18,2）），0）平均成绩

从学生m左加入m.SID = n.SID上的SC n

按m.SID分组，m.Sname

）t2，其中平均成绩> t1。平均成绩）+ 1

（

选择m.SID学生编号，

m.Sname学生姓名，

notull（cast（avg（score）as decimal（18,2）），0）平均成绩

从学生m左加入m.SID = n.SID上的SC n

按m.SID分组，m.Sname

）t1

按px排序

选择t1。*，px =（从以下项中选择count（distinct平均成绩）

（

选择m.SID学生编号，

m.Sname学生姓名，

notull（cast（avg（score）as decimal（18,2）），0）平均成绩

从学生m左加入m.SID = n.SID上的SC n

按m.SID分组，m.Sname

）t2，其中平均成绩> = t1。平均成绩

（

选择m.SID学生编号，

m.Sname学生姓名，

notull（cast（avg（score）as decimal（18,2）），0）平均成绩

从学生m左加入m.SID = n.SID上的SC n

按m.SID分组，m.Sname

）t1

按px排序

–24.2查询学生的平均成绩并进行排名，sql 2005用等级，DENSE_RANK完成，分平均成绩重复时保留名次空缺和不保留名次空缺两种。

选择t。*，px = rank（）over（由平均成绩desc排序）

（

选择m.SID学生编号，

m.Sname学生姓名，

notull（cast（avg（score）as decimal（18,2）），0）平均成绩

从学生m左加入m.SID = n.SID上的SC n

按m.SID分组，m.Sname

）吨

按px排序

选择t。*，px = DENSE_RANK（）over（由平均成绩desc排序）

（

选择m.SID学生编号，

m.Sname学生姓名，

notull（cast（avg（score）as decimal（18,2）），0）平均成绩

从学生m左加入m.SID = n.SID上的SC n

按m.SID分组，m.Sname

）吨

按px排序

–25，查询各科成绩前三名的记录

–25.1分数重复时保留名次空缺

从学生m，SC n中选择m。*，n.CID，n.score，其中m.SID = n.SID和n.score in

（从sc中选择前3个得分，其中CID = n.CID按得分desc排序）按n.CID排序，n.score desc

–25.2分数重复时不保留名次空缺，合并名次

–sql 2000用子查询实现

select * from（从select t。*，px =（从SC选择count（distinct score）其中CID = t.CID并且得分> = t.score）从sc t）m其中px在1到3之间按m.Cid ，m.px

–sql 2005用DENSE_RANK实现

select * from（select t。*，px = DENSE_RANK（）over（从sc t）按cid顺序按得分desc划分）m其中px在1和3之间按m.CID排序，m.px

–26，查询每门课程被选修的学生数

按CID从sc组中选择Cid，count（SID）学生数

–27，查询出只有两门课程的全部学生的学号和姓名

选择Student.SID，Student.Sname

来自SC的学生

其中Student.SID = SC.SID

按Student.SID分组，Student.Sname

具有count（SC.CID）= 2

按Student.SID订购

–28，查询男生，女生人数

从学生那里选择count（Ssex）作为男生人数，其中Ssex = N’男’

从学生那里选择count（Ssex）作为女生人数，其中Ssex = N’女’

选择学生的总和（如果Ssex = N’男’则为1 else 0结束）男生人数，sum（如果学生为Ssex = N’女’则为1 else 0结束）

当Ssex = N’男’然后N’男生人数’else N时，从学生组中选择案例，当Ssex = N’男’然后N’男生人数’else N’女生人数’结束男女情况时“女生人数”结尾

–29，查询名字中含有 “风” 字的学生信息

从学生那里选择*，其名字类似N’％风％’

从charindex（N’风’，sname）> 0的学生中选择*

–30，查询同名同性学生名单，并统计同名人数

从Student组中选择Sname学生姓名，count（）人数，按Sname的count（）> 1

–31，查询1990年出生的学生名单（注：学生表中圣人列的类型是日期时间）

从学生那里选择*年（sage）= 1990

从学生那里选择*，其中datediff（yy，sage，‘1990-01-01’）= 0

在日期部分（yy，sage）= 1990的学生中选择*

从学生那里选择* convert（varchar（4），sage，120）=‘1990’

–32，查询每门课程的平均成绩，结果按平均成绩降序排列，平均成绩相同时，按课程编号升序排列

选择m.CID，m.Cname，cast（avg（n.score）作为十进制（18,2））avg_score

来自课程m，SC n

其中m.CID = n.CID

按m.CID分组

按avg_score desc，m.CID asc排序

–33，查询平均成绩大于等于85的所有学生的学号，姓名和平均成绩

选择a.SID，a.Sname，cast（avg（b.score）作为十进制（18,2））avg_score

来自学生a，sc b

其中a.SID = b.SID

按a.SID分组

具有cast（avg（b.score）作为十进制（18,2））> = 85

通过a.SID订购

–34，查询课程名称为 “数学”，且分数低于60的学生姓名和分数

选择sname，得分

学生，SC，课程

其中SC.SID = Student.SID和SC.CID = Course.CID和Course.Cname = N’数学’且得分<60

–35，查询所有学生的课程及分数情况;

选择Student。*，Course.Cname，SC.CID，SC.score

来自SC学生，课程

其中Student.SID = SC.SID和SC.CID = Course.CID

按Student.SID排序

–36，查询任何一门课程成绩在70分以上的姓名，课程名称和分数;

选择Student。*，Course.Cname，SC.CID，SC.score

来自SC学生，课程

其中Student.SID = SC.SID和SC.CID = Course.CID和SC.score> = 70

按Student.SID排序

–37，查询不及格的课程

选择Student。*，Course.Cname，SC.CID，SC.score

来自SC学生，课程

其中Student.SID = SC.SID和SC.CID = Course.CID和SC.score <60

按Student.SID排序

–38，查询课程编号为01且课程成绩在80分以上的学生的学号和姓名;

选择Student。*，Course.Cname，SC.CID，SC.score

来自SC学生，课程

其中Student.SID = SC.SID和SC.CID = Course.CID和SC.CID ='01’并且SC.score> = 80

按Student.SID排序

–39，求每门课程的学生人数

选择Course.CID，Course.Cname，count（*）学生人数

来自课程

其中Course.CID = SC.CID

按Course.CID分组，Course.Cname

按Course.CID排序，Course.Cname

–40，查询选修 “张三” 老师所授课程的学生中，成绩最高的学生信息及其成绩

–40.1当最高分只有一个时

选择排名前1位的Student。*，Course.Cname，SC.CID，SC.score

来自学生，SC，课程，老师

其中Student.SID = SC.SID和SC.CID = Course.CID和Course.TID = Teacher.TID和Teacher.Tname = N’张三’

由SC.score desc订购

–40.2当最高分出现多个时

选择Student。*，Course.Cname，SC.CID，SC.score

来自学生，SC，课程，老师

其中Student.SID = SC.SID和SC.CID = Course.CID和Course.TID = Teacher.TID和Teacher.Tname = N’张三’和

SC.score =（从SC，课程，教师中选择max（SC.score），其中SC.CID = Course.CID和Course.TID = Teacher.TID和Teacher.Tname = N’张三’）

–41，查询不同课程成绩相同的学生的学生编号，课程编号，学生成绩

• 方法1

从SC m中选择m。*，（选择CID，从SC组中按CID得分，count（1）> 1的得分）n

其中m.CID = n.CID和m.score = n.score按m.CID排序，m.score，m.SID

• 方法2

从存在的SC m中选择m。*（从（选择CID，从SC组按CID评分，计数（1）> 1的评分中选择1）n

其中m.CID = n.CID和m.score = n.score）按m.CID，m.score和m.SID排序

–42，查询每门功成绩最好的前两名

从分数中的sc t中选择t。*（从分数中选择CID = T.CID的sc中排名靠前的2个分数），按t.CID，t.score desc进行排序

–43，统计每门课程的学生选修人数（超过5人的课程才统计）。要求输出课程号和选修人数，查询结果按人数降序排列，若人数相同，按课程号升序排列

选择Course.CID，Course.Cname，count（*）学生人数

来自课程

其中Course.CID = SC.CID

按Course.CID分组，Course.Cname

计数（*）> = 5

按学生人数desc排序，Course.CID

–44，检索至少选修两门课程的学生学号

选择student.SID，student.Sname

来自学生

其中student.SID = SC.SID

按student.SID分组，student.Sname

具有count（1）> = 2

按学生订购

–45，查询选修了全部课程的学生信息

-方法1根据数量来完成

从SID所在的学生中选择学生。*

（通过具有count（1）=（从过程中选择count（1）的SID）从sc组中选择SID）

-方法2使用双重否定来完成

从学生t中选择t。*，而不是t.SID

（

从中选择不同的m.SID

（

从学生，课程中选择SID，CID

）不存在的m（从sc n中选择1，其中n.SID = m.SID和n.CID = m.CID）

）

-方法3使用双重否定来完成

从不存在的学生t中选择t。*（从中选择1

（

从中选择不同的m.SID

（

从学生，课程中选择SID，CID

）不存在的m（从sc n中选择1，其中n.SID = m.SID和n.CID = m.CID）

）k其中k.SID = t.SID

）

–46，查询各学生的年龄

–46.1只按照年份来算

从学生中选择*，datediff（yy，sage，getdate（））年龄

–46.2按照出生日期来算，当前月日<出生年月的月日则，年龄减一

选择*，当right（convert（varchar（10），getdate（），120），5）<right（convert（varchar（10），sage，120），5）接下来的情况下datediff（yy，sage，getdate（） ）-1个来自学生的datediff（yy，sage，getdate（））结束年龄

–47，查询本周过生日的学生

从学生那里选择* datediff（week，datename（yy，getdate（））+ right（convert（varchar（10），sage，120），6），getdate（））= 0的学生

–48，查询下周过生日的学生

从学生那里选择*，其中datediff（week，datename（yy，getdate（））+ right（convert（varchar（10），sage，120），6），getdate（））= -1

–49，查询本月过生日的学生

从学生那里选择*，其中datediff（mm，datename（yy，getdate（））+ right（convert（varchar（10），sage，120），6），getdate（））= 0

–50，查询下月过生日的学生

从学生那里选择* datediff（mm，datename（yy，getdate（））+ right（convert（varchar（10），sage，120），6），getdate（））= -1

放下桌子学生，课程，老师，SC