数据透视表中级班练习十

JLxiangwei · 发表于 2014-6-15 16:05

本帖最后由 JLxiangwei 于 2014-9-16 14:58 编辑

箫风 · 发表于 2014-6-15 22:40

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

天空的雨 · 发表于 2014-6-20 09:58

本帖最后由天空的雨于 2014-6-23 08:43 编辑

答：
28.查询选修“叶平”老师所授课程的学生中，成绩最高的学生姓名及其成绩

select StuName,CourseName,t1.Score
from (((select A.CourseId,MAX(Score) AS Score
         from([tblScore$]A
         INNER JOIN [tblCourse$]B ON A.CourseId= B.CourseId)
         INNER JOIN [tblTeacher$]C ON B.TeaId=C.TeaId
         WHERE TeaName="叶平 "
         group by A.CourseId)t1
inner join [tblScore$]t2 on t1.CourseId=t2.CourseId
                                          and t1.Score=t2.Score)
inner join [tblStudent$]t3 on t2.StuId =t3.StuId)
inner join  [tblCourse$]t4 on t2.CourseId=t4.CourseId

29.查询各个课程及相应的选修人数

select a.CourseId,a.CourseName,Course_count
from[tblCourse$]a
left join (select CourseId,count(StuId) as Course_count
         from[tblScore$]
         group by CourseId)b on a.CourseId=b.CourseId

30.查询不同课程成绩相同的学生的学号、课程号、学生成绩

SELECT StuId,CourseId,Score
FROM [tblScore$]A
WHERE (select count(CourseId)
            from (select  DISTINCT CourseId,Score
                     from[tblScore$])B
            WHERE B.Score =A.Score)>1
ORDER BY Score

31.查询每门功成绩最好的前两名(不考虑重复)

SELECT StuId,CourseId,Score
FROM [tblScore$]A
WHERE (select count(Score)
            from [tblScore$]B
         WHERE B.CourseId =A.CourseId
               AND B.Score >A.Score)<2
ORDER BY CourseId,Score

33.查询两门以上不及格课程的同学的学号及其平均成绩

SELECT StuId,ROUND(AVG(Score),2) AS Score_AVG
FROM[tblScore$]
WHERE  DCOUNT("Score","tblScore$","StuId='" & StuId & "' AND Score<60") >1
GROUP BY StuId

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

xiaoni · 发表于 2014-6-20 21:22

第一题：select t1.stuid,stuname,score from[tblstudent$]t1,[tblcourse$]t2,[tblscore$]t3 where t1.stuid=t3.stuid and t2.coursename="数据库" and t2.courseid=t3.courseid and t3.score<60
第二题：SELECT * from (select stuname,coursename,score from [tblstudent$]t1,[tblcourse$]t2,[tblscore$]t3,[tblteacher$]t4 where t1.stuid=t3.stuid and t2.courseid=t3.courseid and t2.teaid=t4.teaid and teaname="叶平")a where not exists(select coursename from (select stuname,coursename,score from [tblstudent$]t1,[tblcourse$]t2,[tblscore$]t3,[tblteacher$]t4 where t1.stuid=t3.stuid and t2.courseid=t3.courseid and t2.teaid=t4.teaid and teaname="叶平")b where a.coursename=b.coursename and a.score<b.score)
第三题：select t2.courseid,coursename,course_count from (select courseid,count(*) as course_count from [tblscore$] group by courseid)t1 right join [tblcourse$]t2 on t1.courseid=t2.courseid
第四题解法1：select * from [tblscore$]a where (select count(*) from[tblscore$]b where a.score=b.score and a.courseid<>b.courseid)>=1 order by courseid,score desc
第四题解法2：select distinct a.* from[tblscore$]a inner join [tblscore$]b on a.courseid<>b.courseid and a.score=b.score order by a.courseid,a.score desc
第四题解法3：select distinct a.* from[tblscore$]a,[tblscore$]b where a.courseid<>b.courseid and a.score=b.score order by a.courseid,a.score desc
第五题解法1：select a.* from [tblscore$] a where a.stuid in (select top 2 b.stuid from [tblscore$]b where b.courseid= a.courseid order by score desc) order by courseid,stuid
第五题解法2：SELECT A.* FROM [tblscore$] A WHERE (SELECT 1+COUNT(*) FROM [tblscore$] B WHERE B.courseid=A.courseid AND B.score>A.score)<=2 ORDER BY courseid, score DESC
第六题：select t1.Stuid,format(avg(score),"0.00") as Score_Avg from [tblscore$]t1,(select stuid from [tblscore$] where score <60 group by stuid having count(*)>=2)t2 where t1.stuid=t2.stuid group by t1.stuid

车仁静 · 发表于 2014-6-23 16:04

23

SELECT StuName,Score
FROM [tblstudent$]A,[tblCourse$]B,[tblScore$]C
WHERE A.StuId=C.StuId AND B.CourseId=C.CourseId AND CourseName="数据库" AND Score<60

复制代码

28

SELECT StuName,CourseName,Score
FROM [tblCourse$]A, [tblScore$]B,[tblStudent$]C,[tblTeacher$]D
WHERE D.TeaId = A.TeaId AND A.CourseId = B.CourseId AND B.StuId = C.StuId AND TeaName='叶平'
AND Score=(SELECT MAX(Score) FROM [tblScore$] WHERE CourseId=A.CourseId)

复制代码

29

SELECT A.CourseId,CourseName,COUNT(StuId) AS [Course_count]
FROM [tblCourse$]A
LEFT JOIN [tblScore$]B ON A.CourseId=B.CourseId
GROUP BY A.CourseId,CourseName

复制代码

30

SELECT *
FROM [tblScore$]
WHERE Score IN(SELECT Score
FROM (SELECT Score,CourseId
FROM [tblScore$]
GROUP BY Score,CourseId)
GROUP BY Score
HAVING COUNT(CourseId)>1)
ORDER BY Score DESC,StuId,CourseId

复制代码

31

SELECT *
FROM [tblScore$]A
WHERE Score IN (SELECT TOP 2 Score
FROM [tblScore$]B
WHERE A.CourseId=B.CourseId
ORDER BY CourseId,Score DESC)
ORDER BY CourseId

复制代码

33

SELECT StuId,FORMAT(AVG(Score),"#.00") AS Score_Avg
FROM [tblScore$]
WHERE StuId IN(SELECT StuId
FROM [tblScore$]
WHERE Score<60
GROUP BY StuId
HAVING COUNT(CourseId)>1)
GROUP BY StuId

复制代码

无影无形 · 发表于 2014-6-30 17:01

我感觉后3个思路应该差不多，就是不知道哪卡住了。。

jio1ye · 发表于 2014-7-16 21:43

第一题：这题题目应该出错啦。因为和之前的重复了。得到的结果和演示的结果是不一样的。

select T1.StuID,StuName,Score from ([tblscore$]T1 left join [tblstudent$]T2 on T1.stuid=T2.stuid) where courseid = (select CourseID from [tblCourse$] where CourseName = "数据库") and Score <60

复制代码

第二题：

select * from (Select T3.StuName,CourseName,Score from (([tblscore$]T1 left join [tblcourse$]T2 on T2.CourseID=T1.CourseID) left join [tblstudent$]T3 on T3.StuID=T1.StuID) where T1.courseid in (Select CourseID from [tblCourse$] where TeaID in (select TeaID from [tblTeacher$] where TeaName = "叶平"))) where CourseName & Score in (Select CourseName&Max(Score) as Score from(Select T3.StuName,CourseName,Score from (([tblscore$]T1 left join [tblcourse$]T2 on T2.CourseID=T1.CourseID) left join [tblstudent$]T3 on T3.StuID=T1.StuID) where T1.courseid in (Select CourseID from [tblCourse$] where TeaID in (select TeaID from [tblTeacher$] where TeaName = "叶平"))) group by CourseName)

复制代码

第三题：

Select T2.CourseID,CourseName,Course_Count from ((select CourseID,Count(CourseID) as Course_Count from [tblscore$] group by CourseID)T1 right join [tblcourse$]T2 on T1.CourseID=T2.courseID)

复制代码

第四题：

select * from [tblscore$] where score in (select score from (select courseid,score from [tblscore$] group by courseid,score) group by score having count(courseid)>1) order by score desc

复制代码

第五题：

select * from [tblscore$] where courseid&score in (Select courseid&max(score) from [tblscore$] where courseid & score & stuid not in (Select courseid & score & stuid from [tblscore$] where courseid&score in (Select courseid&max(score) from [tblScore$] group by courseid)) group by courseid) and courseid not in (Select courseid from [tblscore$] where courseid&score in (Select courseid&max(score) from [tblScore$] group by courseid) group by courseid having count(courseid)>1) union Select * from [tblscore$] where courseid&score in (Select courseid&max(score) from [tblScore$] group by courseid) order by courseid

复制代码

第六题：

Select StuID,format(Avg(Score),"0.00") as Score_Avg from [tblscore$] where StuID in (select StuID from [tblscore$] where score<60 group by stuid having count(stuid)>1) group by stuid

复制代码

zx_wl · 发表于 2014-7-28 16:09

23、

select t1.StuId,stuName,Score from (select * from [tblScore$] where CourseId=(select CourseId from [tblCourse$] where CourseName='数据库') and Score<60)t1 left join [tblStudent$]t2 on t1.StuId=t2.StuId

复制代码

28、

select t5.StuName,CourseName,Score from((select * from(select *,(select count(*) from [tblScore$]t2 where t1.CourseId=CourseId and t1.Score<t2.Score) as 名次 from [tblScore$]t1 where CourseId in(select CourseId from [tblCourse$] where TeaId=(select TeaId from [tblTeacher$] where TeaName='叶平')))t3 where 名次<1)t4 left join [tblStudent$]t5 on t4.StuId=t5.StuId) left join [tblCourse$]t6 on t4.CourseId=t6.CourseId

复制代码

29、

select t1.CourseId,CourseName,iif(count(Score)=0,null,count(Score)) as Course_count from [tblCourse$]t1 left join [tblScore$]t2 on t1.CourseId=t2.CourseId group by t1.CourseId,CourseName

复制代码

30、

select * from [tblScore$] where Score in(select Score from(select count(CourseId),Score from(select distinct CourseId,Score from [tblScore$] where Score in(select Score from [tblScore$] group by Score having count(Score)>1)) group by Score having count(CourseId)>1)) order by Score desc

复制代码

31、

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 名<2

复制代码

33、

select StuId,format(avg(Score),"0.00") as Score_Avg from [tblScore$] where StuId in(select StuId from[tblScore$] where Score<60 group by StuId having count(CourseId)>1) group by StuId

复制代码

vbyou127 · 发表于 2015-12-30 19:23

fgffgfg

ZZCUEEI · 发表于 2015-12-30 21:05

学习学习

		自动登录	找回密码
密码			注册