资源描述：
《试论鲁迅精神对萧军小说创作的影响的论文》由会员上传分享，免费在线阅读，更多相关内容在学术论文-天天文库。
原文地址：sql语句多表查询(学生表/课程表/教师表/成绩表 )作者：海豚湾孬蛋问题及描述：--1.学生表Student(S#,Sname,Sage,Ssex)--S#学生编号,Sname学生姓名,Sage出生年月,Ssex学生性别--2.课程表Course(C#,Cname,T#)--C#--课程编号,Cname课程名称,T#教师编号--3.教师表Teacher(T#,Tname)--T#教师编号,Tname教师姓名--4.成绩表SC(S#,C#,score)--S#学生编号,C#课程编号,score分数*/--创建测试数据createtableStudent(S#varchar(10),Snamenvarchar(10),Sagedatetime,Ssexnvarchar(10))insertintoStudentvalues('01',N'赵雷','1990-01-01',N'男')insertintoStudentvalues('02',N'钱电','1990-12-21',N'男')insertintoStudentvalues('03',N'孙风','1990-05-20',N'男')insertintoStudentvalues('04',N'李云','1990-08-06',N'男')insertintoStudentvalues('05',N'周梅','1991-12-01',N'女')insertintoStudentvalues('06',N'吴兰','1992-03-01',N'女')insertintoStudentvalues('07',N'郑竹','1989-07-01',N'女')insertintoStudentvalues('08',N'王菊','1990-01-20',N'女')createtableCourse(C#varchar(10),Cnamenvarchar(10),T#varchar(10))insertintoCoursevalues('01',N'语文','02')insertintoCoursevalues('02',N'数学','01')insertintoCoursevalues('03',N'英语','03')createtableTeacher(T#varchar(10),Tnamenvarchar(10))insertintoTeachervalues('01',N'张三') insertintoTeachervalues('02',N'李四')insertintoTeachervalues('03',N'王五')createtableSC(S#varchar(10),C#varchar(10),scoredecimal(18,1))insertintoSCvalues('01','01',80)insertintoSCvalues('01','02',90)insertintoSCvalues('01','03',99)insertintoSCvalues('02','01',70)insertintoSCvalues('02','02',60)insertintoSCvalues('02','03',80)insertintoSCvalues('03','01',80)insertintoSCvalues('03','02',80)insertintoSCvalues('03','03',80)insertintoSCvalues('04','01',50)insertintoSCvalues('04','02',30)insertintoSCvalues('04','03',20)insertintoSCvalues('05','01',76)insertintoSCvalues('05','02',87)insertintoSCvalues('06','01',31)insertintoSCvalues('06','03',34)insertintoSCvalues('07','02',89)insertintoSCvalues('07','03',98)go--1、查询"01"课程比"02"课程成绩高的学生的信息及课程分数--1.1、查询同时存在"01"课程和"02"课程的情况selecta.*,b.score[课程'01'的分数],c.score[课程'02'的分数]fromStudenta,SCb,SCcwherea.S#=b.S#anda.S#=c.S#andb.C#='01'andc.C#='02'andb.score>c.score--1.2、查询同时存在"01"课程和"02"课程的情况和存在"01"课程但可能不存在"02"课程的情况(不存在时显示为null)(以下存在相同内容时不再解释) selecta.*,b.score[课程"01"的分数],c.score[课程"02"的分数]fromStudentaleftjoinSCbona.S#=b.S#andb.C#='01'leftjoinSCcona.S#=c.S#andc.C#='02'whereb.score>isnull(c.score,0)--2、查询"01"课程比"02"课程成绩低的学生的信息及课程分数--2.1、查询同时存在"01"课程和"02"课程的情况selecta.*,b.score[课程'01'的分数],c.score[课程'02'的分数]fromStudenta,SCb,SCcwherea.S#=b.S#anda.S#=c.S#andb.C#='01'andc.C#='02'andb.score=60orderbya.S#--4、查询平均成绩小于60分的同学的学生编号和学生姓名和平均成绩--4.1、查询在sc表存在成绩的学生信息的SQL语句。selecta.S#,a.Sname,cast(avg(b.score)asdecimal(18,2))avg_scorefromStudenta,scbwherea.S#=b.S#groupbya.S#,a.Snamehavingcast(avg(b.score)asdecimal(18,2))<60 orderbya.S#--4.2、查询在sc表中不存在成绩的学生信息的SQL语句。selecta.S#,a.Sname,isnull(cast(avg(b.score)asdecimal(18,2)),0)avg_scorefromStudentaleftjoinscbona.S#=b.S#groupbya.S#,a.Snamehavingisnull(cast(avg(b.score)asdecimal(18,2)),0)<60orderbya.S#--5、查询所有同学的学生编号、学生姓名、选课总数、所有课程的总成绩--5.1、查询所有有成绩的SQL。selecta.S#[学生编号],a.Sname[学生姓名],count(b.C#)选课总数,sum(score)[所有课程的总成绩]fromStudenta,SCbwherea.S#=b.S#groupbya.S#,a.Snameorderbya.S#--5.2、查询所有(包括有成绩和无成绩)的SQL。selecta.S#[学生编号],a.Sname[学生姓名],count(b.C#)选课总数,sum(score)[所有课程的总成绩]fromStudentaleftjoinSCbona.S#=b.S#groupbya.S#,a.Snameorderbya.S#--6、查询"李"姓老师的数量--方法1selectcount(Tname)["李"姓老师的数量]fromTeacherwhereTnamelikeN'李%'--方法2selectcount(Tname)["李"姓老师的数量]fromTeacherwhereleft(Tname,1)=N'李' --7、查询学过"张三"老师授课的同学的信息selectdistinctStudent.*fromStudent,SC,Course,TeacherwhereStudent.S#=SC.S#andSC.C#=Course.C#andCourse.T#=Teacher.T#andTeacher.Tname=N'张三'orderbyStudent.S#--8、查询没学过"张三"老师授课的同学的信息selectm.*fromStudentmwhereS#notin(selectdistinctSC.S#fromSC,Course,TeacherwhereSC.C#=Course.C#andCourse.T#=Teacher.T#andTeacher.Tname=N'张三')orderbym.S#--9、查询学过编号为"01"并且也学过编号为"02"的课程的同学的信息--方法1selectStudent.*fromStudent,SCwhereStudent.S#=SC.S#andSC.C#='01'andexists(Select1fromSCSC_2whereSC_2.S#=SC.S#andSC_2.C#='02')orderbyStudent.S#--方法2selectStudent.*fromStudent,SCwhereStudent.S#=SC.S#andSC.C#='02'andexists(Select1fromSCSC_2whereSC_2.S#=SC.S#andSC_2.C#='01')orderbyStudent.S#--方法3selectm.*fromStudentmwhereS#in( selectS#from ( selectdistinctS#fromSCwhereC#='01' unionall selectdistinctS#fromSCwhereC#='02' )tgroupbyS#havingcount(1)=2 )orderbym.S#--10、查询学过编号为"01"但是没有学过编号为"02"的课程的同学的信息--方法1selectStudent.*fromStudent,SCwhereStudent.S#=SC.S#andSC.C#='01'andnotexists(Select1fromSCSC_2whereSC_2.S#=SC.S#andSC_2.C#='02')orderbyStudent.S#--方法2selectStudent.*fromStudent,SCwhereStudent.S#=SC.S#andSC.C#='01'andStudent.S#notin(SelectSC_2.S#fromSCSC_2whereSC_2.S#=SC.S#andSC_2.C#='02')orderbyStudent.S#--11、查询没有学全所有课程的同学的信息--11.1、selectStudent.*fromStudent,SCwhereStudent.S#=SC.S#groupbyStudent.S#,Student.Sname,Student.Sage,Student.Ssexhavingcount(C#)<(selectcount(C#)fromCourse)--11.2selectStudent.*fromStudentleftjoinSConStudent.S#=SC.S#groupbyStudent.S#,Student.Sname,Student.Sage,Student.Ssexhavingcount(C#)<(selectcount(C#)fromCourse)--12、查询至少有一门课与学号为"01"的同学所学相同的同学的信息selectdistinctStudent.*fromStudent,SCwhereStudent.S#=SC.S#andSC.C#in(selectC#fromSCwhereS#='01')andStudent.S#<>'01' --13、查询和"01"号的同学学习的课程完全相同的其他同学的信息selectStudent.*fromStudentwhereS#in(selectdistinctSC.S#fromSCwhereS#<>'01'andSC.C#in(selectdistinctC#fromSCwhereS#='01')groupbySC.S#havingcount(1)=(selectcount(1)fromSCwhereS#='01'))--14、查询没学过"张三"老师讲授的任一门课程的学生姓名selectstudent.*fromstudentwherestudent.S#notin(selectdistinctsc.S#fromsc,course,teacherwheresc.C#=course.C#andcourse.T#=teacher.T#andteacher.tname=N'张三')orderbystudent.S#--15、查询两门及其以上不及格课程的同学的学号，姓名及其平均成绩selectstudent.S#,student.sname,cast(avg(score)asdecimal(18,2))avg_scorefromstudent,scwherestudent.S#=SC.S#andstudent.S#in(selectS#fromSCwherescore<60groupbyS#havingcount(1)>=2)groupbystudent.S#,student.sname--16、检索"01"课程分数小于60，按分数降序排列的学生信息selectstudent.*,sc.C#,sc.scorefromstudent,scwherestudent.S#=SC.S#andsc.score<60andsc.C#='01'orderbysc.scoredesc--17、按平均成绩从高到低显示所有学生的所有课程的成绩以及平均成绩--17.1SQL2000静态selecta.S#学生编号,a.Sname学生姓名, max(casec.CnamewhenN'语文'thenb.scoreelsenullend)[语文], max(casec.CnamewhenN'数学'thenb.scoreelsenullend)[数学], max(casec.CnamewhenN'英语'thenb.scoreelsenullend)[英语], cast(avg(b.score)asdecimal(18,2))平均分 fromStudentaleftjoinSCbona.S#=b.S#leftjoinCourseconb.C#=c.C#groupbya.S#,a.Snameorderby平均分desc--17.2SQL2000动态declare@sqlnvarchar(4000)set@sql='selecta.S#'+N'学生编号'+',a.Sname'+N'学生姓名'select@sql=@sql+',max(casec.CnamewhenN'''+Cname+'''thenb.scoreelsenullend)['+Cname+']'from(selectdistinctCnamefromCourse)astset@sql=@sql+',cast(avg(b.score)asdecimal(18,2))'+N'平均分'+'fromStudentaleftjoinSCbona.S#=b.S#leftjoinCourseconb.C#=c.C#groupbya.S#,a.Snameorderby'+N'平均分'+'desc'exec(@sql)--24、查询学生平均成绩及其名次--24.1查询学生的平均成绩并进行排名，sql2000用子查询完成，分平均成绩重复时保留名次空缺和不保留名次空缺两种。selectt1.*,px=(selectcount(1)from( selectm.S#[学生编号], m.Sname[学生姓名], isnull(cast(avg(score)asdecimal(18,2)),0)[平均成绩] fromStudentmleftjoinSCnonm.S#=n.S# groupbym.S#,m.Sname)t2where平均成绩>t1.平均成绩)+1from( selectm.S#[学生编号], m.Sname[学生姓名], isnull(cast(avg(score)asdecimal(18,2)),0)[平均成绩] fromStudentmleftjoinSCnonm.S#=n.S# groupbym.S#,m.Sname)t1orderbypxselectt1.*,px=(selectcount(distinct平均成绩)from( selectm.S#[学生编号], m.Sname[学生姓名], isnull(cast(avg(score)asdecimal(18,2)),0)[平均成绩] fromStudentmleftjoinSCnonm.S#=n.S# groupbym.S#,m.Sname)t2where平均成绩>=t1.平均成绩)from( selectm.S#[学生编号], m.Sname[学生姓名], isnull(cast(avg(score)asdecimal(18,2)),0)[平均成绩] fromStudentmleftjoinSCnonm.S#=n.S# groupbym.S#,m.Sname)t1orderbypx--24.2查询学生的平均成绩并进行排名，sql2005用rank,DENSE_RANK完成，分平均成绩重复时保留名次空缺和不保留名次空缺两种。selectt.*,px=rank()over(orderby[平均成绩]desc)from( selectm.S#[学生编号], m.Sname[学生姓名], isnull(cast(avg(score)asdecimal(18,2)),0)[平均成绩] fromStudentmleftjoinSCnonm.S#=n.S# groupbym.S#,m.Sname)torderbypxselectt.*,px=DENSE_RANK()over(orderby[平均成绩]desc)from( selectm.S#[学生编号], m.Sname[学生姓名], isnull(cast(avg(score)asdecimal(18,2)),0)[平均成绩] fromStudentmleftjoinSCnonm.S#=n.S# groupbym.S#,m.Sname)torderbypx --25、查询各科成绩前三名的记录--25.1分数重复时保留名次空缺selectm.*,n.C#,n.scorefromStudentm,SCnwherem.S#=n.S#andn.scorein(selecttop3scorefromscwhereC#=n.C#orderbyscoredesc)orderbyn.C#,n.scoredesc--25.2分数重复时不保留名次空缺，合并名次--sql2000用子查询实现select*from(selectt.*,px=(selectcount(distinctscore)fromSCwhereC#=t.C#andscore>=t.score)fromsct)mwherepxbetween1and3orderbym.c#,m.px--sql2005用DENSE_RANK实现select*from(selectt.*,px=DENSE_RANK()over(partitionbyc#orderbyscoredesc)fromsct)mwherepxbetween1and3orderbym.C#,m.px --26、查询每门课程被选修的学生数selectc#,count(S#)[学生数]fromscgroupbyC#--27、查询出只有两门课程的全部学生的学号和姓名selectStudent.S#,Student.SnamefromStudent,SCwhereStudent.S#=SC.S#groupbyStudent.S#,Student.Snamehavingcount(SC.C#)=2orderbyStudent.S#--28、查询男生、女生人数selectcount(Ssex)as男生人数fromStudentwhereSsex=N'男'selectcount(Ssex)as女生人数fromStudentwhereSsex=N'女'selectsum(casewhenSsex=N'男'then1else0end)[男生人数],sum(casewhenSsex=N'女'then1else0end)[女生人数]fromstudentselectcasewhenSsex=N'男'thenN'男生人数'elseN'女生人数'end[男女情况],count(1)[人数]fromstudentgroupbycasewhenSsex=N'男'thenN'男生人数'elseN'女生人数'end--29、查询名字中含有"风"字的学生信息select*fromstudentwheresnamelikeN'%风%'select*fromstudentwherecharindex(N'风',sname)>0--30、查询同名同性学生名单，并统计同名人数selectSname[学生姓名],count(*)[人数]fromStudentgroupbySnamehavingcount(*)>1--31、查询1990年出生的学生名单(注：Student表中Sage列的类型是datetime)select*fromStudentwhereyear(sage)=1990select*fromStudentwheredatediff(yy,sage,'1990-01-01')=0 select*fromStudentwheredatepart(yy,sage)=1990select*fromStudentwhereconvert(varchar(4),sage,120)='1990'--32、查询每门课程的平均成绩，结果按平均成绩降序排列，平均成绩相同时，按课程编号升序排列selectm.C#,m.Cname,cast(avg(n.score)asdecimal(18,2))avg_scorefromCoursem,SCnwherem.C#=n.C# groupbym.C#,m.Cnameorderbyavg_scoredesc,m.C#asc--33、查询平均成绩大于等于85的所有学生的学号、姓名和平均成绩selecta.S#,a.Sname,cast(avg(b.score)asdecimal(18,2))avg_scorefromStudenta,scbwherea.S#=b.S#groupbya.S#,a.Snamehavingcast(avg(b.score)asdecimal(18,2))>=85orderbya.S#--34、查询课程名称为"数学"，且分数低于60的学生姓名和分数selectsname,scorefromStudent,SC,CoursewhereSC.S#=Student.S#andSC.C#=Course.C#andCourse.Cname=N'数学'andscore<60--35、查询所有学生的课程及分数情况；selectStudent.*,Course.Cname,SC.C#,SC.score fromStudent,SC,CoursewhereStudent.S#=SC.S#andSC.C#=Course.C#orderbyStudent.S#,SC.C# --36、查询任何一门课程成绩在70分以上的姓名、课程名称和分数；selectStudent.*,Course.Cname,SC.C#,SC.score fromStudent,SC,CoursewhereStudent.S#=SC.S#andSC.C#=Course.C#andSC.score>=70orderbyStudent.S#,SC.C#--37、查询不及格的课程selectStudent.*,Course.Cname,SC.C#,SC.score fromStudent,SC,CoursewhereStudent.S#=SC.S#andSC.C#=Course.C#andSC.score<60orderbyStudent.S#,SC.C#--38、查询课程编号为01且课程成绩在80分以上的学生的学号和姓名；selectStudent.*,Course.Cname,SC.C#,SC.score fromStudent,SC,CoursewhereStudent.S#=SC.S#andSC.C#=Course.C#andSC.C#='01'andSC.score>=80orderbyStudent.S#,SC.C#--39、求每门课程的学生人数selectCourse.C#,Course.Cname,count(*)[学生人数]fromCourse,SCwhereCourse.C#=SC.C#groupby Course.C#,Course.CnameorderbyCourse.C#,Course.Cname--40、查询选修"张三"老师所授课程的学生中，成绩最高的学生信息及其成绩--40.1当最高分只有一个时selecttop1Student.*,Course.Cname,SC.C#,SC.score fromStudent,SC,Course,TeacherwhereStudent.S#=SC.S#andSC.C#=Course.C#andCourse.T#=Teacher.T#andTeacher.Tname=N'张三' orderbySC.scoredesc--40.2当最高分出现多个时selectStudent.*,Course.Cname,SC.C#,SC.score fromStudent,SC,Course,TeacherwhereStudent.S#=SC.S#andSC.C#=Course.C#andCourse.T#=Teacher.T#andTeacher.Tname=N'张三'andSC.score=(selectmax(SC.score)fromSC,Course,TeacherwhereSC.C#=Course.C#andCourse.T#=Teacher.T#andTeacher.Tname=N'张三')--41、查询不同课程成绩相同的学生的学生编号、课程编号、学生成绩--方法1selectm.*fromSCm,(selectC#,scorefromSCgroupbyC#,scorehavingcount(1)>1)nwherem.C#=n.C#andm.score=n.scoreorderbym.C#,m.score,m.S#--方法2selectm.*fromSCmwhereexists(select1from(selectC#,scorefromSCgroupbyC#,scorehavingcount(1)>1)nwherem.C#=n.C#andm.score=n.score)orderbym.C#,m.score,m.S#--42、查询每门功成绩最好的前两名selectt.*fromsctwherescorein(selecttop2scorefromscwhereC#=T.C#orderbyscoredesc)orderbyt.C#,t.scoredesc--43、统计每门课程的学生选修人数（超过5人的课程才统计）。要求输出课程号和选修人数，查询结果按人数降序排列，若人数相同，按课程号升序排列 selectCourse.C#,Course.Cname,count(*)[学生人数]fromCourse,SCwhereCourse.C#=SC.C#groupby Course.C#,Course.Cnamehavingcount(*)>=5orderby[学生人数]desc,Course.C# --44、检索至少选修两门课程的学生学号selectstudent.S#,student.Snamefromstudent,SCwherestudent.S#=SC.S#groupbystudent.S#,student.Snamehavingcount(1)>=2orderbystudent.S#--45、查询选修了全部课程的学生信息--方法1根据数量来完成selectstudent.*fromstudentwhereS#in(selectS#fromscgroupbyS#havingcount(1)=(selectcount(1)fromcourse))--方法2使用双重否定来完成selectt.*fromstudenttwheret.S#notin( selectdistinctm.S#from ( selectS#,C#fromstudent,course )mwherenotexists(select1fromscnwheren.S#=m.S#andn.C#=m.C#))--方法3使用双重否定来完成selectt.*fromstudenttwherenotexists(select1from( selectdistinctm.S#from ( selectS#,C#fromstudent,course )mwherenotexists(select1fromscnwheren.S#=m.S#andn.C#=m.C#))kwherek.S#=t.S#) --46、查询各学生的年龄--46.1只按照年份来算select*,datediff(yy,sage,getdate())[年龄]fromstudent--46.2按照出生日期来算，当前月日<出生年月的月日则，年龄减一select*,casewhenright(convert(varchar(10),getdate(),120),5)
