50个经典SQL查询语句.pdf

《50个经典SQL查询语句.pdf》由会员分享，可在线阅读，更多相关《50个经典SQL查询语句.pdf（14页珍藏版）》请在taowenge.com淘文阁网|工程机械CAD图纸|机械工程制图|CAD装配图下载|SolidWorks_CaTia_CAD_UG_PROE_设计图分享下载上搜索。

1、-一个题目涉及到的50 个 Sql语句-（下面表的结构以给出,自己在数据库中建立表.并且添加相应的数据,数据要全面些.其中 Student 表中,SId为学生的 ID）-表结构-学生表 tblStudent（编号 StuId、姓名 StuName、年龄 StuAge、性别 StuSex）-课程表 tblCourse（课程编号 CourseId、课程名称 CourseName、教师编号 TeaId）-成绩表 tblScore（学生编号 StuId、课程编号 CourseId、成绩 Score）-教师表 tblTeacher（教师编号 TeaId、姓名 TeaName）-1、查询“001”课程比“

2、002”课程成绩高的所有学生的学号；Select StuId From tblStudent s1 Where(Select Score From tblScore t1 Where t1.StuId=s1.stuId And t1.CourseId=001)(Select Score From tblScore t2 Where t2.StuId=s1.stuId And t2.CourseId=002)-2、查询平均成绩大于60 分的同学的学号和平均成绩；Select StuId,Avg(Score)as AvgScore From tblScore Group By StuId Havi

3、ng Avg(Score)60-3、查询所有同学的学号、姓名、选课数、总成绩；Select StuId,StuName,SelCourses=(Select Count(CourseId)From tblScore t1 Where t1.StuId=s1.StuId),SumScore=(Select Sum(Score)From tblScore t2 Where t2.StuId=s1.StuId)From tblStudent s1-4、查询姓“李”的老师的个数；Select Count(*)From tblTeacher Where TeaName like 李%-5、查询没学过“叶

4、平”老师课的同学的学号、姓名；Select StuId,StuName From tblStudent Where StuId Not In(Select StuID From tblScore sc Inner Join tblCourse cu ON sc.CourseId=cu.CourseId Inner Join tblTeacher tc ON cu.TeaId=tc.TeaId Where tc.TeaName=叶平)-6、查询学过“001”并且也学过编号“002”课程的同学的学号、姓名；Select StuId,StuName From tblStudent st Where(

5、Select Count(*)From tblScore s1 Where s1.StuId=st.StuId And s1.CourseId=001)0 And(Select Count(*)From tblScore s2 Where s2.StuId=st.StuId And s2.CourseId=002)0-7、查询学过“叶平”老师所教的所有课的同学的学号、姓名；Select StuId,StuName From tblStudent st Where not exists(Select CourseID From tblCourse cu Inner Join tblTeacher

6、 tc On cu.TeaID=tc.TeaID Where tc.TeaName=叶平 And CourseID not in(Select CourseID From tblScore Where StuID=st.StuID)-8、查询课程编号“002”的成绩比课程编号“001”课程低的所有同学的学号、姓名；Select StuId,StuName From tblStudent s1 Where(Select Score From tblScore t1 Where t1.StuId=s1.stuId And t1.CourseId=001)(Select Score From tbl

7、Score t2 Where t2.StuId=s1.stuId And t2.CourseId=002)-9、查询所有课程成绩小于60 分的同学的学号、姓名；Select StuId,StuName From tblStudent st Where StuId Not IN(Select StuId From tblScore sc Where st.StuId=sc.StuId And Score60)-10、查询没有学全所有课的同学的学号、姓名；Select StuId,StuName From tblStudent st Where(Select Count(*)From tblSco

8、re sc Where st.StuId=sc.StuId)(Select Count(*)From tblCourse)-11、查询至少有一门课与学号为“1001”的同学所学相同的同学的学号和姓名；-运用连接查询Select DistInct st.StuId,StuName From tblStudent st Inner Join tblScore sc ON st.StuId=sc.StuId Where sc.CourseId IN(Select CourseId From tblScore Where StuId=1001)-嵌套子查询Select StuId,StuName Fr

9、om tblStudent 文档编码:CT4N7H9K6T6 HP2L6J3O4D1 ZJ6Q5A5B1G7文档编码:CT4N7H9K6T6 HP2L6J3O4D1 ZJ6Q5A5B1G7文档编码:CT4N7H9K6T6 HP2L6J3O4D1 ZJ6Q5A5B1G7文档编码:CT4N7H9K6T6 HP2L6J3O4D1 ZJ6Q5A5B1G7文档编码:CT4N7H9K6T6 HP2L6J3O4D1 ZJ6Q5A5B1G7文档编码:CT4N7H9K6T6 HP2L6J3O4D1 ZJ6Q5A5B1G7文档编码:CT4N7H9K6T6 HP2L6J3O4D1 ZJ6Q5A5B1G7文档编码:C

10、T4N7H9K6T6 HP2L6J3O4D1 ZJ6Q5A5B1G7文档编码:CT4N7H9K6T6 HP2L6J3O4D1 ZJ6Q5A5B1G7文档编码:CT4N7H9K6T6 HP2L6J3O4D1 ZJ6Q5A5B1G7文档编码:CT4N7H9K6T6 HP2L6J3O4D1 ZJ6Q5A5B1G7文档编码:CT4N7H9K6T6 HP2L6J3O4D1 ZJ6Q5A5B1G7文档编码:CT4N7H9K6T6 HP2L6J3O4D1 ZJ6Q5A5B1G7文档编码:CT4N7H9K6T6 HP2L6J3O4D1 ZJ6Q5A5B1G7文档编码:CT4N7H9K6T6 HP2L6J3O4

11、D1 ZJ6Q5A5B1G7文档编码:CT4N7H9K6T6 HP2L6J3O4D1 ZJ6Q5A5B1G7文档编码:CT4N7H9K6T6 HP2L6J3O4D1 ZJ6Q5A5B1G7文档编码:CT4N7H9K6T6 HP2L6J3O4D1 ZJ6Q5A5B1G7文档编码:CT4N7H9K6T6 HP2L6J3O4D1 ZJ6Q5A5B1G7文档编码:CT4N7H9K6T6 HP2L6J3O4D1 ZJ6Q5A5B1G7文档编码:CT4N7H9K6T6 HP2L6J3O4D1 ZJ6Q5A5B1G7文档编码:CT4N7H9K6T6 HP2L6J3O4D1 ZJ6Q5A5B1G7文档编码:C

12、T4N7H9K6T6 HP2L6J3O4D1 ZJ6Q5A5B1G7文档编码:CT4N7H9K6T6 HP2L6J3O4D1 ZJ6Q5A5B1G7文档编码:CT4N7H9K6T6 HP2L6J3O4D1 ZJ6Q5A5B1G7文档编码:CT4N7H9K6T6 HP2L6J3O4D1 ZJ6Q5A5B1G7文档编码:CT4N7H9K6T6 HP2L6J3O4D1 ZJ6Q5A5B1G7文档编码:CT4N7H9K6T6 HP2L6J3O4D1 ZJ6Q5A5B1G7文档编码:CT4N7H9K6T6 HP2L6J3O4D1 ZJ6Q5A5B1G7文档编码:CT4N7H9K6T6 HP2L6J3O4

13、D1 ZJ6Q5A5B1G7文档编码:CT4N7H9K6T6 HP2L6J3O4D1 ZJ6Q5A5B1G7文档编码:CT4N7H9K6T6 HP2L6J3O4D1 ZJ6Q5A5B1G7文档编码:CT4N7H9K6T6 HP2L6J3O4D1 ZJ6Q5A5B1G7文档编码:CT4N7H9K6T6 HP2L6J3O4D1 ZJ6Q5A5B1G7文档编码:CT4N7H9K6T6 HP2L6J3O4D1 ZJ6Q5A5B1G7文档编码:CT4N7H9K6T6 HP2L6J3O4D1 ZJ6Q5A5B1G7文档编码:CT4N7H9K6T6 HP2L6J3O4D1 ZJ6Q5A5B1G7文档编码:C

14、T4N7H9K6T6 HP2L6J3O4D1 ZJ6Q5A5B1G7文档编码:CT4N7H9K6T6 HP2L6J3O4D1 ZJ6Q5A5B1G7文档编码:CT4N7H9K6T6 HP2L6J3O4D1 ZJ6Q5A5B1G7文档编码:CT4N7H9K6T6 HP2L6J3O4D1 ZJ6Q5A5B1G7文档编码:CT4N7H9K6T6 HP2L6J3O4D1 ZJ6Q5A5B1G7文档编码:CT4N7H9K6T6 HP2L6J3O4D1 ZJ6Q5A5B1G7文档编码:CT4N7H9K6T6 HP2L6J3O4D1 ZJ6Q5A5B1G7文档编码:CT4N7H9K6T6 HP2L6J3O4

15、D1 ZJ6Q5A5B1G7文档编码:CT4N7H9K6T6 HP2L6J3O4D1 ZJ6Q5A5B1G7文档编码:CT4N7H9K6T6 HP2L6J3O4D1 ZJ6Q5A5B1G7文档编码:CT4N7H9K6T6 HP2L6J3O4D1 ZJ6Q5A5B1G7Where StuId In(Select Distinct StuId From tblScore Where CourseId In(Select CourseId From tblScore Where StuId=1001)-12、查询至少学过学号为“1001”同学所有课程的其他同学学号和姓名；Select StuId,S

16、tuName From tblStudent Where StuId In(Select Distinct StuId From tblScore Where CourseId Not In(Select CourseId From tblScore Where StuId=1001)-13、把“SC”表中“叶平”老师教的课的成绩都更改为此课程的平均成绩；(从子查询中获取父查询中的表名，这样也行？)-创建测试表Select*Into Sc From tblScore go Update Sc Set Score=(Select Avg(Score)From tblScore s1 Where

17、s1.CourseId=sc.CourseId)Where CourseId IN(Select CourseId From tblCourse cs INNER JOIN tblTeacher tc ON cs.TeaID=tc.TeaID WHERE TeaName=叶平)-14、查询和“1002”号的同学学习的课程完全相同的其他同学学号和姓名；Select StuID,StuName From tblStudent st Where StuId 1002 And Not Exists(Select*From tblScore sc Where sc.StuId=st.StuId And

18、CourseId Not In(Select CourseId From tblScore Where StuId=1002)And Not Exists(Select*From tblScore Where StuId=1002 And CourseId Not In(Select CourseId From tblScore sc Where sc.StuId=st.StuId)-15、删除学习“叶平”老师课的SC表记录；Delete From tblScore Where CourseId IN(Select CourseId From tblCourse cs INNER JOIN t

19、blTeacher tc ON cs.TeaId=tc.TeaId Where tc.TeaName=叶平)-16、向 SC表中插入一些记录，这些记录要求符合以下条件：没有上过编号“003”课程的同学学号、002号课的平均成绩；Insert Into tblScore(StuId,CourseId,Score)文档编码:CT4N7H9K6T6 HP2L6J3O4D1 ZJ6Q5A5B1G7文档编码:CT4N7H9K6T6 HP2L6J3O4D1 ZJ6Q5A5B1G7文档编码:CT4N7H9K6T6 HP2L6J3O4D1 ZJ6Q5A5B1G7文档编码:CT4N7H9K6T6 HP2L6J3

20、O4D1 ZJ6Q5A5B1G7文档编码:CT4N7H9K6T6 HP2L6J3O4D1 ZJ6Q5A5B1G7文档编码:CT4N7H9K6T6 HP2L6J3O4D1 ZJ6Q5A5B1G7文档编码:CT4N7H9K6T6 HP2L6J3O4D1 ZJ6Q5A5B1G7文档编码:CT4N7H9K6T6 HP2L6J3O4D1 ZJ6Q5A5B1G7文档编码:CT4N7H9K6T6 HP2L6J3O4D1 ZJ6Q5A5B1G7文档编码:CT4N7H9K6T6 HP2L6J3O4D1 ZJ6Q5A5B1G7文档编码:CT4N7H9K6T6 HP2L6J3O4D1 ZJ6Q5A5B1G7文档编码

21、:CT4N7H9K6T6 HP2L6J3O4D1 ZJ6Q5A5B1G7文档编码:CT4N7H9K6T6 HP2L6J3O4D1 ZJ6Q5A5B1G7文档编码:CT4N7H9K6T6 HP2L6J3O4D1 ZJ6Q5A5B1G7文档编码:CT4N7H9K6T6 HP2L6J3O4D1 ZJ6Q5A5B1G7文档编码:CT4N7H9K6T6 HP2L6J3O4D1 ZJ6Q5A5B1G7文档编码:CT4N7H9K6T6 HP2L6J3O4D1 ZJ6Q5A5B1G7文档编码:CT4N7H9K6T6 HP2L6J3O4D1 ZJ6Q5A5B1G7文档编码:CT4N7H9K6T6 HP2L6J3

22、O4D1 ZJ6Q5A5B1G7文档编码:CT4N7H9K6T6 HP2L6J3O4D1 ZJ6Q5A5B1G7文档编码:CT4N7H9K6T6 HP2L6J3O4D1 ZJ6Q5A5B1G7文档编码:CT4N7H9K6T6 HP2L6J3O4D1 ZJ6Q5A5B1G7文档编码:CT4N7H9K6T6 HP2L6J3O4D1 ZJ6Q5A5B1G7文档编码:CT4N7H9K6T6 HP2L6J3O4D1 ZJ6Q5A5B1G7文档编码:CT4N7H9K6T6 HP2L6J3O4D1 ZJ6Q5A5B1G7文档编码:CT4N7H9K6T6 HP2L6J3O4D1 ZJ6Q5A5B1G7文档编码

23、:CT4N7H9K6T6 HP2L6J3O4D1 ZJ6Q5A5B1G7文档编码:CT4N7H9K6T6 HP2L6J3O4D1 ZJ6Q5A5B1G7文档编码:CT4N7H9K6T6 HP2L6J3O4D1 ZJ6Q5A5B1G7文档编码:CT4N7H9K6T6 HP2L6J3O4D1 ZJ6Q5A5B1G7文档编码:CT4N7H9K6T6 HP2L6J3O4D1 ZJ6Q5A5B1G7文档编码:CT4N7H9K6T6 HP2L6J3O4D1 ZJ6Q5A5B1G7文档编码:CT4N7H9K6T6 HP2L6J3O4D1 ZJ6Q5A5B1G7文档编码:CT4N7H9K6T6 HP2L6J3

24、O4D1 ZJ6Q5A5B1G7文档编码:CT4N7H9K6T6 HP2L6J3O4D1 ZJ6Q5A5B1G7文档编码:CT4N7H9K6T6 HP2L6J3O4D1 ZJ6Q5A5B1G7文档编码:CT4N7H9K6T6 HP2L6J3O4D1 ZJ6Q5A5B1G7文档编码:CT4N7H9K6T6 HP2L6J3O4D1 ZJ6Q5A5B1G7文档编码:CT4N7H9K6T6 HP2L6J3O4D1 ZJ6Q5A5B1G7文档编码:CT4N7H9K6T6 HP2L6J3O4D1 ZJ6Q5A5B1G7文档编码:CT4N7H9K6T6 HP2L6J3O4D1 ZJ6Q5A5B1G7文档编码

25、:CT4N7H9K6T6 HP2L6J3O4D1 ZJ6Q5A5B1G7文档编码:CT4N7H9K6T6 HP2L6J3O4D1 ZJ6Q5A5B1G7文档编码:CT4N7H9K6T6 HP2L6J3O4D1 ZJ6Q5A5B1G7文档编码:CT4N7H9K6T6 HP2L6J3O4D1 ZJ6Q5A5B1G7文档编码:CT4N7H9K6T6 HP2L6J3O4D1 ZJ6Q5A5B1G7文档编码:CT4N7H9K6T6 HP2L6J3O4D1 ZJ6Q5A5B1G7文档编码:CT4N7H9K6T6 HP2L6J3O4D1 ZJ6Q5A5B1G7Select StuId,002,(Select

26、 Avg(Score)From tblScore Where CourseId=002)From tblScore Where StuId Not In(Select StuId From tblScore Where CourseId=003)-17、按平均成绩从高到低显示所有学生的“数据库”、“企业管理”、“英语”三门的课程成绩，按如下形式显示：学生 ID,数据库,企业管理,英语,有效课程数,有效平均分Select StuId,数据库=(Select Score From tblScore sc Inner Join tblCourse cs On sc.CourseId=cs.Cours

27、eId Where CourseName=数据库 And sc.StuID=st.StuId),企业管理=(Select Score From tblScore sc Inner Join tblCourse cs On sc.CourseId=cs.CourseId Where CourseName=企业管理 And sc.StuID=st.StuId),英语=(Select Score From tblScore sc Inner Join tblCourse cs On sc.CourseId=cs.CourseId Where CourseName=英语 And sc.StuID=st

28、.StuId),有效课程数=(Select Count(Score)From tblScore sc Inner Join tblCourse cs On sc.CourseId=cs.CourseId Where(CourseName=数据库 or CourseName=企业管理 or CourseName=英语)And sc.StuID=st.StuId),有效平均分=(Select Avg(Score)From tblScore sc Inner Join tblCourse cs On sc.CourseId=cs.CourseId Where(CourseName=数据库 or Co

29、urseName=企业管理 or CourseName=英语)And sc.StuID=st.StuId)From tblStudent st Order by 有效平均分Desc-18、查询各科成绩最高和最低的分：以如下形式显示：课程ID，最高分，最低分Select CourseId as 课程 ID,最高分=(Select Max(Score)From tblScore sc Where sc.CourseId=cs.CourseId),最低分=(Select Min(Score)From tblScore sc Where sc.CourseId=cs.CourseId)From tbl

30、Course cs-19、按各科平均成绩从低到高和及格率的百分数从高到低顺序(百分数后如何格式化为两位小数?)Select 课程 ID,平均分,及格率 From(Select CourseId as 课程 ID,平均分=(Select Avg(Score)From tblScore sc Where sc.CourseId=cs.CourseId),及格率=Convert(varchar(10),(Select Count(*)From tblScore sc Where sc.CourseId=cs.CourseId And sc.Score=60)*10000/(Select Count(

31、*)From tblScore sc Where sc.CourseId=cs.CourseId)/100)+%From tblScore cs)as tmp Group by 课程 ID,平均分,及格率Order by 平均分,Convert(float,substring(及格率,1,len(及格率)-1)Desc 文档编码:CT4N7H9K6T6 HP2L6J3O4D1 ZJ6Q5A5B1G7文档编码:CT4N7H9K6T6 HP2L6J3O4D1 ZJ6Q5A5B1G7文档编码:CT4N7H9K6T6 HP2L6J3O4D1 ZJ6Q5A5B1G7文档编码:CT4N7H9K6T6 HP

32、2L6J3O4D1 ZJ6Q5A5B1G7文档编码:CT4N7H9K6T6 HP2L6J3O4D1 ZJ6Q5A5B1G7文档编码:CT4N7H9K6T6 HP2L6J3O4D1 ZJ6Q5A5B1G7文档编码:CT4N7H9K6T6 HP2L6J3O4D1 ZJ6Q5A5B1G7文档编码:CT4N7H9K6T6 HP2L6J3O4D1 ZJ6Q5A5B1G7文档编码:CT4N7H9K6T6 HP2L6J3O4D1 ZJ6Q5A5B1G7文档编码:CT4N7H9K6T6 HP2L6J3O4D1 ZJ6Q5A5B1G7文档编码:CT4N7H9K6T6 HP2L6J3O4D1 ZJ6Q5A5B1G

33、7文档编码:CT4N7H9K6T6 HP2L6J3O4D1 ZJ6Q5A5B1G7文档编码:CT4N7H9K6T6 HP2L6J3O4D1 ZJ6Q5A5B1G7文档编码:CT4N7H9K6T6 HP2L6J3O4D1 ZJ6Q5A5B1G7文档编码:CT4N7H9K6T6 HP2L6J3O4D1 ZJ6Q5A5B1G7文档编码:CT4N7H9K6T6 HP2L6J3O4D1 ZJ6Q5A5B1G7文档编码:CT4N7H9K6T6 HP2L6J3O4D1 ZJ6Q5A5B1G7文档编码:CT4N7H9K6T6 HP2L6J3O4D1 ZJ6Q5A5B1G7文档编码:CT4N7H9K6T6 HP

34、2L6J3O4D1 ZJ6Q5A5B1G7文档编码:CT4N7H9K6T6 HP2L6J3O4D1 ZJ6Q5A5B1G7文档编码:CT4N7H9K6T6 HP2L6J3O4D1 ZJ6Q5A5B1G7文档编码:CT4N7H9K6T6 HP2L6J3O4D1 ZJ6Q5A5B1G7文档编码:CT4N7H9K6T6 HP2L6J3O4D1 ZJ6Q5A5B1G7文档编码:CT4N7H9K6T6 HP2L6J3O4D1 ZJ6Q5A5B1G7文档编码:CT4N7H9K6T6 HP2L6J3O4D1 ZJ6Q5A5B1G7文档编码:CT4N7H9K6T6 HP2L6J3O4D1 ZJ6Q5A5B1G

35、7文档编码:CT4N7H9K6T6 HP2L6J3O4D1 ZJ6Q5A5B1G7文档编码:CT4N7H9K6T6 HP2L6J3O4D1 ZJ6Q5A5B1G7文档编码:CT4N7H9K6T6 HP2L6J3O4D1 ZJ6Q5A5B1G7文档编码:CT4N7H9K6T6 HP2L6J3O4D1 ZJ6Q5A5B1G7文档编码:CT4N7H9K6T6 HP2L6J3O4D1 ZJ6Q5A5B1G7文档编码:CT4N7H9K6T6 HP2L6J3O4D1 ZJ6Q5A5B1G7文档编码:CT4N7H9K6T6 HP2L6J3O4D1 ZJ6Q5A5B1G7文档编码:CT4N7H9K6T6 HP

36、2L6J3O4D1 ZJ6Q5A5B1G7文档编码:CT4N7H9K6T6 HP2L6J3O4D1 ZJ6Q5A5B1G7文档编码:CT4N7H9K6T6 HP2L6J3O4D1 ZJ6Q5A5B1G7文档编码:CT4N7H9K6T6 HP2L6J3O4D1 ZJ6Q5A5B1G7文档编码:CT4N7H9K6T6 HP2L6J3O4D1 ZJ6Q5A5B1G7文档编码:CT4N7H9K6T6 HP2L6J3O4D1 ZJ6Q5A5B1G7文档编码:CT4N7H9K6T6 HP2L6J3O4D1 ZJ6Q5A5B1G7文档编码:CT4N7H9K6T6 HP2L6J3O4D1 ZJ6Q5A5B1G

37、7文档编码:CT4N7H9K6T6 HP2L6J3O4D1 ZJ6Q5A5B1G7文档编码:CT4N7H9K6T6 HP2L6J3O4D1 ZJ6Q5A5B1G7文档编码:CT4N7H9K6T6 HP2L6J3O4D1 ZJ6Q5A5B1G7文档编码:CT4N7H9K6T6 HP2L6J3O4D1 ZJ6Q5A5B1G7文档编码:CT4N7H9K6T6 HP2L6J3O4D1 ZJ6Q5A5B1G7文档编码:CT4N7H9K6T6 HP2L6J3O4D1 ZJ6Q5A5B1G7文档编码:CT4N7H9K6T6 HP2L6J3O4D1 ZJ6Q5A5B1G7-20、查询如下课程平均成绩和及格率的

38、百分数(用1 行显示):企业管理（001），马克思（002），OO&UML（003），数据库（004）Select 课程 ID=sc.CourseId,课程名称=cs.CourseName,平均成绩=Avg(Score),及格率=Convert(varchar(10),(Select Count(Score)From tblScore Where CourseId=sc.CourseId And Score=60)*10000/Count(Score)/100.0)+%From tblScore sc Inner Join tblCourse cs ON sc.CourseId=cs.Cour

39、seId Where sc.CourseId like 001234 Group By sc.CourseId,cs.CourseName-21、查询不同老师所教不同课程平均分从高到低显示Select 课程 ID=CourseId,课程名称=CourseName,授课教师=TeaName,平均成绩=(Select Avg(Score)From tblScore Where CourseId=cs.CourseId)From tblCourse cs Inner Join tblTeacher tc ON cs.TeaId=tc.TeaId Order by 平均成绩Desc-22、查询如下课程

40、成绩第3 名到第 6 名的学生成绩单：企业管理（001），马克思（002），UML（003），数据库（004）格式：学生 ID,学生姓名,企业管理,马克思,UML,数据库,平均成绩Select*From(Select Top 6 学生 ID=StuId,学生姓名=StuName,企业管理=(Select Score From tblScore sc Inner Join tblCourse cs On sc.CourseId=cs.CourseId Where CourseName=企业管理 And sc.StuID=st.StuId),马克思=(Select Score From tblSc

41、ore sc Inner Join tblCourse cs On sc.CourseId=cs.CourseId Where CourseName=马克思 And sc.StuID=st.StuId),UML=(Select Score From tblScore sc Inner Join tblCourse cs On sc.CourseId=cs.CourseId Where CourseName=UML And sc.StuID=st.StuId),数据库=(Select Score From tblScore sc Inner Join tblCourse cs On sc.Cou

42、rseId=cs.CourseId Where CourseName=数据库 And sc.StuID=st.StuId),平均成绩=(Select Avg(Score)From tblScore sc Inner Join tblCourse cs On sc.CourseId=cs.CourseId Where(CourseName=数据库 or CourseName=企业管理 or CourseName=UMLor CourseName=马克思)And sc.StuID=st.StuId),排名=Row_Number()Over(Order by(Select Avg(Score)Fro

43、m tblScore sc Inner Join tblCourse cs On sc.CourseId=cs.CourseId Where(CourseName=数据库 or CourseName=企业管理 or CourseName=UMLor CourseName=马克思)And sc.StuID=st.StuId)DESC)From tblStudent st Order by 排名)as tmp Where 排名 between 3 And 6 文档编码:CT4N7H9K6T6 HP2L6J3O4D1 ZJ6Q5A5B1G7文档编码:CT4N7H9K6T6 HP2L6J3O4D1 Z

44、J6Q5A5B1G7文档编码:CT4N7H9K6T6 HP2L6J3O4D1 ZJ6Q5A5B1G7文档编码:CT4N7H9K6T6 HP2L6J3O4D1 ZJ6Q5A5B1G7文档编码:CT4N7H9K6T6 HP2L6J3O4D1 ZJ6Q5A5B1G7文档编码:CT4N7H9K6T6 HP2L6J3O4D1 ZJ6Q5A5B1G7文档编码:CT4N7H9K6T6 HP2L6J3O4D1 ZJ6Q5A5B1G7文档编码:CT4N7H9K6T6 HP2L6J3O4D1 ZJ6Q5A5B1G7文档编码:CT4N7H9K6T6 HP2L6J3O4D1 ZJ6Q5A5B1G7文档编码:CT4N7

45、H9K6T6 HP2L6J3O4D1 ZJ6Q5A5B1G7文档编码:CT4N7H9K6T6 HP2L6J3O4D1 ZJ6Q5A5B1G7文档编码:CT4N7H9K6T6 HP2L6J3O4D1 ZJ6Q5A5B1G7文档编码:CT4N7H9K6T6 HP2L6J3O4D1 ZJ6Q5A5B1G7文档编码:CT4N7H9K6T6 HP2L6J3O4D1 ZJ6Q5A5B1G7文档编码:CT4N7H9K6T6 HP2L6J3O4D1 ZJ6Q5A5B1G7文档编码:CT4N7H9K6T6 HP2L6J3O4D1 ZJ6Q5A5B1G7文档编码:CT4N7H9K6T6 HP2L6J3O4D1 Z

46、J6Q5A5B1G7文档编码:CT4N7H9K6T6 HP2L6J3O4D1 ZJ6Q5A5B1G7文档编码:CT4N7H9K6T6 HP2L6J3O4D1 ZJ6Q5A5B1G7文档编码:CT4N7H9K6T6 HP2L6J3O4D1 ZJ6Q5A5B1G7文档编码:CT4N7H9K6T6 HP2L6J3O4D1 ZJ6Q5A5B1G7文档编码:CT4N7H9K6T6 HP2L6J3O4D1 ZJ6Q5A5B1G7文档编码:CT4N7H9K6T6 HP2L6J3O4D1 ZJ6Q5A5B1G7文档编码:CT4N7H9K6T6 HP2L6J3O4D1 ZJ6Q5A5B1G7文档编码:CT4N7

47、H9K6T6 HP2L6J3O4D1 ZJ6Q5A5B1G7文档编码:CT4N7H9K6T6 HP2L6J3O4D1 ZJ6Q5A5B1G7文档编码:CT4N7H9K6T6 HP2L6J3O4D1 ZJ6Q5A5B1G7文档编码:CT4N7H9K6T6 HP2L6J3O4D1 ZJ6Q5A5B1G7文档编码:CT4N7H9K6T6 HP2L6J3O4D1 ZJ6Q5A5B1G7文档编码:CT4N7H9K6T6 HP2L6J3O4D1 ZJ6Q5A5B1G7文档编码:CT4N7H9K6T6 HP2L6J3O4D1 ZJ6Q5A5B1G7文档编码:CT4N7H9K6T6 HP2L6J3O4D1 Z

48、J6Q5A5B1G7文档编码:CT4N7H9K6T6 HP2L6J3O4D1 ZJ6Q5A5B1G7文档编码:CT4N7H9K6T6 HP2L6J3O4D1 ZJ6Q5A5B1G7文档编码:CT4N7H9K6T6 HP2L6J3O4D1 ZJ6Q5A5B1G7文档编码:CT4N7H9K6T6 HP2L6J3O4D1 ZJ6Q5A5B1G7文档编码:CT4N7H9K6T6 HP2L6J3O4D1 ZJ6Q5A5B1G7文档编码:CT4N7H9K6T6 HP2L6J3O4D1 ZJ6Q5A5B1G7文档编码:CT4N7H9K6T6 HP2L6J3O4D1 ZJ6Q5A5B1G7文档编码:CT4N7

49、H9K6T6 HP2L6J3O4D1 ZJ6Q5A5B1G7文档编码:CT4N7H9K6T6 HP2L6J3O4D1 ZJ6Q5A5B1G7文档编码:CT4N7H9K6T6 HP2L6J3O4D1 ZJ6Q5A5B1G7文档编码:CT4N7H9K6T6 HP2L6J3O4D1 ZJ6Q5A5B1G7文档编码:CT4N7H9K6T6 HP2L6J3O4D1 ZJ6Q5A5B1G7文档编码:CT4N7H9K6T6 HP2L6J3O4D1 ZJ6Q5A5B1G7文档编码:CT4N7H9K6T6 HP2L6J3O4D1 ZJ6Q5A5B1G7文档编码:CT4N7H9K6T6 HP2L6J3O4D1 Z

50、J6Q5A5B1G7文档编码:CT4N7H9K6T6 HP2L6J3O4D1 ZJ6Q5A5B1G7-23、统计列印各科成绩,各分数段人数:课程 ID,课程名称,100-85,85-70,70-60,60 Select 课程 ID=CourseId,课程名称=CourseName,100-85=(Select Count(*)From tblScore sc Where CourseId=cs.CourseId And Score between 85 And 100),85-70=(Select Count(*)From tblScore sc Where CourseId=cs.Cours