数据透视表中级班练习三
本帖最后由 JLxiangwei 于 2014-5-27 12:47 编辑先复习，做好后答案直接上传就行
------------------------------------------------------------------------------------
开贴
请参看其他同学的答案，附上解析视频
http://www.excelpx.com/thread-325760-1-1.html
本帖最后由 云影 于 2014-5-14 18:17 编辑
多谢老师指点，已修改{:011:}
练习2SELECT count(*) as Mrli_CountFROM where teaname like "李%"练习13select Courseid as 课程ID,max(Score) AS 最高分,min(Score) AS 最低分 from group by Courseid练习19select Stusex,count(*) AS StuSex_count from GROUP BY Stusex练习20select StuName,COUNT(*) as Stu_Count from group by StuNamehaving COUNT(*)>1练习21select Courseid,ROUND(Avg(score),2) as Score_Avg from group by CourseId order by Avg(score) ASC, Courseid DESC练习32select Stuid from group by Stuid having count(*)>1练习34select Stuid,Courseid,Score from where Courseid ='004'and Score <60 order by Stuid desc, Score desc 2SELECT StuId,ROUND(AVG(Score),2) AS AvgScore FROM GROUP BY StuId HAVING AVG(Score)>604SELECT COUNT(TeaName) AS Mrli_Count FROM WHERE TeaName LIKE '李%'13SELECT CourseId AS 课程ID,MAX(Score) AS 最高分,MIN(Score) AS 最低分 FROM GROUP BY CourseId19SELECT Stusex AS StuSex,COUNT(Stusex) AS StuSex_count FROM GROUP BY Stusex20SELECT StuName,COUNT(StuName) AS Stu_Count FROM GROUP BY StuName HAVING COUNT(StuName)>221SELECT CourseId,FORMAT(AVG(Score),"#.00") AS Score_Avg FROM GROUP BY CourseId ORDER BY AVG(Score),CourseId DESC32SELECT StuId FROM GROUP BY StuId HAVING COUNT(CourseId)>134SELECT StuId,CourseId,Score FROM WHERE CourseId="004" AND Score<60 ORDER BY Score DESC 本帖最后由 天空的雨 于 2014-6-4 11:04 编辑
2014年5月24日订正：
序号为20的题：查询同名同性学生名单，并统计同名人数？
select StuName as 同名同姓人员名单,(select count(StuName) from where StuName= a.StuName) as 同名人数 froma group by StuName, Stusex having count(Stuage)>=2
2.select * from (select StuId,avg(Score) as AvgScore from group by stuid) where avgscore>60
4.select count(*) as li_count from where left(teaname,1)="李"
13.select CourseId,max(score) as 最高分,min(score) as 最低分 from group by courseid
19.select StuSex,count(*) as Sex_Count from group by stusex
20.select * from (select stuname,count(*) as name_count from group by stuname) where name_count>1
21.select CourseId,avg(score) as AvgScore from group by courseid order by avg(score) ,courseid desc
32.select StuId from (select stuid,count(*) as course_count from group by stuid) where course_count>=2
34.select StuId,CourseID,Score from where Courseid="004" and score < 60 order by score desc
SELECT * FROM (SELECT StuId,AVG(Score) AS AvgScore FROM GROUP BY StuId) WHERE AvgScore>60
SELECT COUNT(TeaName) AS 个数 FROM WHERE LEFT(TeaName,1)='李'
SELECT CourseId AS 课程ID,MAX(Score) AS 最高分,MIN(Score) AS 最低分 FROM GROUP BY CourseId
SELECT Stusex,COUNT(Stusex) AS StuSex_count FROM GROUP BY Stusex ORDER BY StuSex DESC
SELECT StuName,Stu_Count FROM (SELECT StuName,COUNT(StuName) AS Stu_Count FROM GROUP BY StuName) WHERE Stu_Count>1
SELECT CourseId,FORMAT(AVG(Score),"0.00") AS Score_Avg FROM GROUP BY CourseId ORDER BY AVG(Score),CourseId DESC
SELECT StuId FROM (SELECT StuId,COUNT(CourseId) AS a FROM GROUP BY StuId) WHERE a>=2
SELECT StuId,CourseId,Score FROM WHERE CourseId='004' AND Score<60 ORDER BY Score DESC这个。感觉绕弯路了，麻烦老师看看了 结果2、select StuId,round(sum(score)/count(stuid),2) as AvgSoure from group by stuid having sum(score)/count(stuid)>60结果4、select count(TeaName) as Mrli_Count from group by TeaName having left(TeaName,1)="李"结果13、select CourseId as 课程ID,max(Score) as 最高分,min( Score) as 最低分 from group by CourseId结果19、select Stusex,count(StuName) as StuSex_count from group by Stusex结果20、select StuName,count(Stuage) as Stu_Count from group by StuName having count(Stuage)>1结果21、select CourseId,format(Avg(Score),"0.00") as Score_Avg from group by CourseId order by Avg(Score)结果32select iif(count(StuID)>1,StuId,null) as StuId from group by StuId having iif(count(StuID)>1,StuId,null) <> null 希望向老师帮忙看看写的是否正确，另上课可以旁听不？
2、SELECT STUID,AVG(SCORE) AS MEAN FROM GROUP BY STUID
4、SELECT COUNT(TEANAME) AS QTY FROM WHERE TEANAME LIKE "李%" GROUP BY TEANAME
13、SELECT CourseId, MIN(Score) AS LOW,MAX(Score) AS HIG FROM GROUP BY CourseId
19、SELECT Stusex,COUNT(*) AS QTY FROM GROUP BY Stusex
20、SELECT StuName,COUNT(*) AS QTY FROM GROUP BY StuName
21、SELECT CourseId,AVG(SCORE) AS MEAN FROM GROUP BY CourseIdORDER BY AVG(SCORE),CourseId DESC
32、SELECT StuId, COUNT(CourseId) FROM GROUP BY StuId having COUNT(CourseId)>1
34、SELECT StuId,CourseId,Score FROM where CourseId="004"andScore < 60 ORDER BY Score DESC 交作业：
第一题：select stuid,round(avg(score),2) as avgsoure from group by stuid having avg(score)>60
第二题：select count(*) as Mrli_count from where teaname like "李%"
第三题：select courseid as 课程ID, max(score) as 最高分，min(score) as 最低分 from group by courseid
第四题：select stusex, count(*) as stusex_count from group by stusex
第五题：select stuname,count(stuname) as stuname_count from group by stuname having count(stuname)>1
第六题：select CourseID, format(Avg(score),"0.00") as Score_Avg from group by courseid order by Avg(score),CourseID desc
每七题：select StuId from group by stuid having count(stuid)>1
第八题：select StuId, CourseId, Score from where CourseId="004" and Score<60 order by Score desc 本帖最后由 午夜洗衣机 于 2014-5-15 22:31 编辑
向老师,怎么每一题都没有子查询或者联合查询的?
交作业:2 select StuId,avg(Score) as AvgScore from group by StuId having avg(Score)>60
4 select count(TeaName) as Mrli_count from where TeaName like "李%"
13 select CourseID as 课程,Max(Score) as 最高分,min(Score) as 最低分 from group by CourseID
19 select Stusex,count(Stusex) as StuSex_count from group by Stusex
20 select StuName,count(StuName) as Stu_Count from group by StuName having count(StuName&Stusex)>1
21 select CourseId,Avg(Score) as Score_Avg from group by CourseId order by Avg(Score),CourseId desc
32 select StuId from group by StuId having count(StuId&CourseId)>1
34 select StuId,CourseId,Score from where CourseId="004" and Score<60 order by Score desc
第32题我自己加了一个子查询,查询至少选修两门课程的学生学号和姓名:select StuId,StuName from where StuId in(select StuId from group by StuId having count(StuId&CourseId)>1) group by StuId,StuName