数据透视表中级班练习九

JLxiangwei

数据透视表中级班练习九.rar (41 KB, 下载次数: 93)

2014-6-9 19:32 上传

点击文件名下载附件

以实际题目为准，结果只做参考

箫风

游客，如果您要查看本帖隐藏内容请回复

天空的雨

答：
12.查询“数据库”成绩从高到低显示所有学生的学号、姓名、成绩
SELECT C.StuId,C.StuName,A.Score
FROM([tblScore$]A
INNER JOIN (SELECT CourseId
                     FROM [tblCourse$]
                      WHERE CourseName="数据库")B ON A.CourseId= B.CourseId)
INNER JOIN [tblStudent$]C ON A.StuId= C.StuId
ORDER BY Score DESC

14.查询按各科平均成绩从低到高和及格率,按照及格率从高到低顺序显示:课程ID，平均分，及格率

SELECT a.CourseId AS 课程ID,平均成绩,FORMAT(及格人次/参考人次,"0%")  as 及格率
FROM(SELECT CourseId,FORMAT(AVG(Score),"0.00") AS 平均成绩,count(Score) as 参考人次
          FROM[tblScore$]
          group by CourseId)A
left join (SELECT CourseId,iif(count(Score)>0,count(Score),0) AS 及格人次
              FROM[tblScore$]
              where Score>=60
             group by CourseId)b  on a.CourseId=b.CourseId
order by 3


15.查询学生平均成绩及其名次

select 学号,平均成绩,(select count(总分)
                                 from(select DISTINCT sum(Score) AS 总分
                                          from[tblScore$]
                                          GROUP BY StuId)b
                               where b.总分>=a.总分 ) as 名次
from(select StuId as 学号,avg(Score) AS 平均成绩,sum(Score) AS 总分
        from[tblScore$]
        group by StuId)a


16.查询各科成绩前三名的记录

SELECT *
FROM[tblScore$]B
WHERE EXISTS(SELECT CourseId,Score
                          FROM[tblScore$]A
            WHERE (SELECT count(Score)
                          FROM(select DISTINCT CourseId,Score
                                     FROM [tblScore$])
                         where CourseId=a.CourseId and  Score>=a.Score) <=2  
         AND A.CourseId=B.CourseId
         AND A.Score=B.Score)
ORDER  BY B.CourseId,B.Score   



22.查询平均成绩大于85的所有学生的学号、姓名和平均成绩

SELECT A.StuId,B.StuName,A.Score_Avg FROM (SELECT StuId,AVG(Score) AS Score_Avg
                                                                           FROM[tblScore$]
                                                                            GROUP BY  StuId
                                                                          HAVING AVG(Score)>85)A
                                                               LEFT JOIN  [tblStudent$]B ON A.StuId=B.StuId



数据透视表中级班练习九-天空的雨答.rar (41.47 KB, 下载次数: 2)

2014-6-10 17:59 上传

点击文件名下载附件

问：FORMAT文本型数字如何转换成数字型数字？

车仁静

12

1. SELECT A.StuId,StuName,Score
   
2. FROM [tblstudent$]A,[tblCourse$]B,[tblScore$]C
   
3. WHERE A.StuId=C.StuId AND B.CourseId=C.CourseId AND CourseName="数据库"
   
4. ORDER BY Score DESC

复制代码

14

1. SELECT CourseId,FORMAT(AVG(Score),"#.00") AS [AVG_Score],FORMAT(SUM(IIF(Score>=60,1,0))/COUNT(Score),"0%") AS [PASS_
   
2. 
   
3. %]   
   
4. FROM [tblScore$]   
   
5. GROUP BY CourseId ORDER BY AVG(Score)

复制代码

15

1. SELECT StuId AS 学号,[AVG_Score],  
   
2.     (SELECT 1+COUNT([AVG_Score])  
   
3.     FROM (SELECT StuId,AVG(score) as [AVG_Score]  
   
4.           FROM [tblScore$]   
   
5.           GROUP BY StuId)A  
   
6.     WHERE [AVG_Score]>B.[AVG_Score]) as Rank
   
7. FROM (SELECT StuId,AVG(score) as [AVG_Score]  
   
8.         FROM [tblScore$]   
   
9.         GROUP BY StuId)B

复制代码

16

1. SELECT CourseId,StuId,Score
   
2. FROM [tblScore$]A
   
3. WHERE Score IN (SELECT TOP 3 Score
   
4.                 FROM [tblScore$]B
   
5.                 WHERE A.CourseId=B.CourseId  
   
6.                 ORDER BY B.CourseId,B.Score DESC)  
   
7. ORDER BY  CourseId,Score DESC,StuId

复制代码

22

1. SELECT A.StuId, StuName,AVG(Score) AS [AVG_Score]
   
2. FROM [tblScore$]A, [tblstudent$]B
   
3. WHERE A.StuId=B.StuId
   
4. GROUP BY A.StuId, StuName HAVING AVG(Score)>85

复制代码

xiaoni

第一题：select t1.stuid,stuname,score from[tblstudent$]t1,[tblcourse$]t2,[tblscore$]t3 where t1.stuid=t3.stuid and t3.courseid=t2.courseid and t2.coursename="数据库" order by scor desc
第二题：select t1.courseid as 课程ID,平均成绩,format(条件个数/总数,"0%") as 及格率 from(select courseid,format(avg(score),"0.00") as 平均成绩,count(*) as 总数 from [tblscore$]group by courseid)t1 left join (select courseid,count(*) as 条件个数 from [tblscore$] where score>60 group by courseid)t2 on t1.courseid=t2.courseid order by 平均成绩
第三题：select stuid,平均成绩,(select count(*)+1 from(select avg(score) as 平均成绩 from [tblscore$] group by stuid)t2 where t2.平均成绩>t1.平均成绩) as 名次 from(select stuid,avg(score) as 平均成绩 from [tblscore$] group by stuid)t1
第四题：select  a.* from [tblscore$] a where  a.stuid in (select top 3 b.stuid from [tblscore$]b where b.courseid= a.courseid order by score desc)
第五题：select t1.stuid,stuname,avg(score) as score_avg from [tblstudent$]t1,[tblscore$]t2 where t1.stuid=t2.stuid  group by t1.stuid,stuname having avg(score)>85

jio1ye

第一题

1. select T1.stuID,stuName,score from ([tblscore$] T1 left join [tblstudent$] T2 on T1.Stuid= T2.stuid) where T1.courseID in (Select courseID from [tblcourse$] where coursename="数据库") order by score desc

复制代码

第二题

1. select T2.CourseID,format(成绩,"0.00") as 平均成绩,format(countjg/countall,"0%") as 及格率 from ((select courseid,count(courseid) as countjg from [tblscore$] where score>60 group by courseid)T1 right join (select courseid,count(courseid) as countall,avg(score) as 成绩 from [tblscore$] group by courseid )T2 on T1.Courseid= T2.Courseid) order by 成绩

复制代码

第三题：主题在网络上百度到了语句思路。没懂。。囧

1. Select *,(Select count(*)+1 from (select stuid as 学号,format(avg(score),"0.00") as 平均成绩 from [tblscore$] group by stuid) T2 where 平均成绩>T1.平均成绩 )  as 名 from (select stuid as 学号,format(avg(score),"0.00") as 平均成绩 from [tblscore$] group by stuid) T1

复制代码

第四题：又臭又长。。。。何必呢。。。。

1. select * from [tblscore$] where courseid & score in ( select courseid & max(score) as score from (select * from [tblscore$] where CourseID & StuID & Score not in (select CourseID & StuID & Score from (select * from [tblscore$] where courseid & score in (Select courseid & max(score) as score from (select * from [tblscore$] where CourseID & StuID & Score not in (select CourseID & StuID & Score from [tblscore$] where CourseID & Score in (select CourseID & Score from (select CourseID,Max(Score) as Score from [tblscore$] group by CourseID)) order by courseid) and courseid in (select courseid  from [tblscore$] where CourseID & Score in (select CourseID & Score from (select CourseID,Max(Score) as Score from [tblscore$] group by CourseID)) group by courseid   having count(courseid)<3)) group by courseid) union select * from [tblscore$] where CourseID & Score in (select CourseID & Score from (select CourseID,Max(Score) as Score from [tblscore$] group by CourseID)))) and courseID in (Select courseID from (select * from [tblscore$] where courseid & score in (Select courseid & max(score) as score from (select * from [tblscore$] where CourseID & StuID & Score not in (select CourseID & StuID & Score from [tblscore$] where CourseID & Score in (select CourseID & Score from (select CourseID,Max(Score) as Score from [tblscore$] group by CourseID)) order by courseid) and courseid in (select courseid  from [tblscore$] where CourseID & Score in (select CourseID & Score from (select CourseID,Max(Score) as Score from [tblscore$] group by CourseID)) group by courseid   having count(courseid)<3)) group by courseid) union select * from [tblscore$] where CourseID & Score in (select CourseID & Score from (select CourseID,Max(Score) as Score from [tblscore$] group by CourseID))) group by courseid having count(courseID)<3)) group by courseid) union select * from [tblscore$] where courseid & score in (Select courseid & max(score) as score from (select * from [tblscore$] where CourseID & StuID & Score not in (select CourseID & StuID & Score from [tblscore$] where CourseID & Score in (select CourseID & Score from (select CourseID,Max(Score) as Score from [tblscore$] group by CourseID)) order by courseid) and courseid in (select courseid  from [tblscore$] where CourseID & Score in (select CourseID & Score from (select CourseID,Max(Score) as Score from [tblscore$] group by CourseID)) group by courseid   having count(courseid)<3)) group by courseid) union select * from [tblscore$] where CourseID & Score in (select CourseID & Score from (select CourseID,Max(Score) as Score from [tblscore$] group by CourseID)) order by courseid

复制代码

第五题

1. select * from (select T1.StuId,stuname,avg(score) as score_avg from ([tblscore$] T1 left join [tblstudent$] T2 on T1.Stuid= T2.stuid) group by T1.stuid,stuname)where score_avg > 85

复制代码

哎。彻底被打败了。。。这个练习就这样了

zx_wl

练习结果12

1. select t1.StuId,StuName,Score from(select StuId,Score from [tblScore$] where CourseId=(select CourseId from [tblCourse$] where CourseName='数据库'))t1 left join [tblStudent$]t2 on t1.StuId=t2.StuId order by Score desc
   

复制代码

练习结果14

1. select * from(select t1.CourseId,平均成绩,format(及格数/科目数,"0%") as 及格率 from(select CourseId,format(avg(Score),"0.00") as 平均成绩,count(Score) as 科目数 from [tblScore$] group by CourseId)t1 left join (select CourseId,iif(count(Score)>0,count(Score),0) as 及格数 from [tblScore$] where Score>59 group by CourseId)t2 on t1.CourseId=t2.CourseId order by format(及格数/科目数,"0%") desc) order by 平均成绩
   

复制代码

练习结果15

1. select t1.StuId,t1.平均成绩,(select count(*) from(select StuId,avg(Score) as 平均成绩 from [tblScore$] group by StuId)t2 where t2.平均成绩>t1.平均成绩)+1 as 名 from(select StuId,avg(Score) as 平均成绩 from [tblScore$] group by StuId)t1
   

复制代码

练习结果16

1. select StuId,CourseId,Score from(select StuId,CourseId,Score,(select count(*) from [tblScore$]t2 where t1.CourseId=t2.CourseId and t1.Score<t2.Score) as 排名 from [tblScore$]t1) where 排名<3
   

复制代码

练习结果22

1. select t1.StuId,StuName,Score_Avg from (select StuId,avg(Score) as Score_Avg from [tblScore$] group by StuId having avg(Score)>85)t1 left join [tblStudent$]t2 on t1.StuId=t2.StuId
   

复制代码

vbyou127

ghhjhjhj