数据透视表中级班练习四

JLxiangwei

数据透视表中级班练习二.rar (22.89 KB, 下载次数: 123)

2014-5-16 09:44 上传

点击文件名下载附件

是不是嫌练习三太简单了
那增加点难度
尽量多方法解题，方法越多越好

午夜洗衣机

交作业:

方法一(字符数236个):

1. select a.StuId,b.StuName
   
2. from (select StuId
   
3. from [tblScore$]
   
4. where CourseId in(select Courseid from [tblCourse$] where coursename in("马克思","企业管理"))
   
5. group by StuId,CourseId)a,
   
6. [tblStudent$]b
   
7. where a.StuId=b.StuId
   
8. group by a.StuId,b.StuName
   
9. having count(a.StuId)>1

复制代码

这次的代码套了好几层,我的代码写得比较啰嗦:

1.select Courseid from [tblCourse$] where coursename in("马克思","企业管理"):

在表格tblCourse中查找课程名为:马克思和企业管理,这两门课对应的课程ID


2.select StuId from [tblScore$] where CourseId in(select Courseid from [tblCourse$] where coursename in("马克思","企业管理")) group by StuId,CourseId:

在表格tblScore中查找选修课程为马克思和企业管理的学生ID,结果集是所有选了马克思或者企业管理的学生ID,只选一门课的学生有一行记录,选了两门课的会有两行记录. "group by StuId,CourseId"是为了下一步Count(StuId)>1做准备.如果这一步只group by StuId,那么下一步Count(StuId)的结果将全部=1......(因为只按学生分组,那每个人就只有一行记录了)


3.select a.StuId,b.StuName from (2中的代码) a,[tblStudent$]b where a.StuId=b.StuId group by a.StuId,b.StuName having count(a.StuId)>1:

2的代码做为临时表,起别名为a,表tblStudent,起别名为b. 然后用a.StuId=b.StuId将两个表格连接起来,having count(a.StuId)>1结果就是有两行记录,同时选了两门课程的学生




方法二(字符数255个,比方法一多...):

1. select a.StuId,b.StuName from [tblScore$]a
   
2. left join
   
3. [tblStudent$]b
   
4. on a.StuId=b.StuId
   
5. where CourseId in(select Courseid from [tblCourse$] where coursename ="马克思") and a.StuId in(select StuId from [tblScore$]
   
6. where CourseId in(select Courseid from [tblCourse$] where coursename ="企业管理"))

复制代码

方法三(字符数191个,最少,而且没有子查询):

1. select a.StuId,c.StuName
   
2. from [tblScore$]a,[tblCourse$]b,[tblStudent$]c
   
3. where a.CourseId=b.CourseId and a.StuId=c.StuId and b.CourseName in("马克思","企业管理")
   
4. group by a.StuId,c.StuName
   
5. having(count(a.StuId)=2)
   

复制代码

把[tblScore$]a,[tblCourse$]b,[tblStudent$]c 三个表连接起来,取课程名是马克思和企业管理的学生ID和姓名
关键是这一句:having(count(a.StuId)=2),筛选同时选择两门课的学生

xdragon

1. select a.StuId,a.StuName from [tblStudent$]a,[tblScore$]b,[tblCourse$]c where
   
2. a.StuId=b.StuId and c.CourseId=b.CourseId and CourseName in ('企业管理','马克思') group
   
3. by a.StuId,a.StuName having count(a.StuId)=2

复制代码

云影

1. select a.StuId,StuName from [tblStudent$]a,[tblScore$]b where a.StuId = b.StuId and CourseId in ('001','002') group by a.StuId,StuName having count(*)>1

复制代码

1. select a.StuId,StuName from(select * from [tblStudent$]a inner join [tblScore$]b on a.StuId = b.StuId) where CourseId in ('001','002') group by a.StuId,StuName having count(*)>1

复制代码

jio1ye

我就只会导入外部数据处理了。
5.数据透视表中级班_练习4.rar (25.43 KB, 下载次数: 4)

2014-5-17 00:11 上传

点击文件名下载附件

天空的雨

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

2014-5-17 11:19 上传

点击文件名下载附件

无影无形

1. select distinct a.stuid,b.stuname from [tblScore$]a,[tblStudent$]b where   a.stuid=b.stuid and a.courseid in("001","002")

复制代码

思路有点混乱，敬请老师指点。

1. select d.stuid,stuname, count(*) as stuid from (select * from [tblstudent$]d left outer join (select a.courseid,a.coursename,b.stuid,b.courseid   from [tblCourse$]a   left outer join [tblscore$]b     on a.courseid=b.courseid where coursename="马克思" or coursename="企业管理")c  on  d.stuid=c.stuid) where coursename<>"" group by d.stuid,stuname having count(*)>1

复制代码

不用子查询不会做count>1的步骤

1. select c.stuid,c.stuname ,count(*) as  stuid from(select a.coursename,c.stuid,c.stuname  from [tblCourse$]a,[tblscore$]b,[tblstudent$]c where a.courseid=b.courseid and b.stuid=c.stuid and  a.coursename in("马克思","企业管理") order by c.stuid ) group by c.stuid,c.stuname having count(*)>1

复制代码

箫风

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

lasharks

先上一个，回头在想其他的

1. select a.StuId,b.StuName from (select count(*) as ct,stuid from (select a.stuid,b.coursename from [tblscore$a:c]a,[tblcourse$a:c]b where a.courseid=b.courseid and (b.coursename="马克思" or b.coursename="企业管理")) group by stuid)a,[tblstudent$]b where a.ct=2 and a.stuid=b.stuid

复制代码

不用子查询了，感觉省了点字符

1. select a.stuid,c.StuName from [tblscore$a:c]a,[tblcourse$a:c]b,[tblStudent$a:b]c where a.courseid=b.courseid and (b.coursename="马克思" or b.coursename="企业管理") and a.stuid=c.stuid group by a.stuid,c.stuname having count(*)=2

复制代码

车仁静

1. SELECT B.StuId, StuName
   
2. FROM [tblCourse$]A,[tblScore$]B,[tblStudent$]C
   
3. WHERE A.CourseId = B.CourseId AND B.StuId = C.StuId
   
4. GROUP BY A.CourseId, A.CourseName, B.StuId, B.CourseId, C.StuId,C.StuName
   
5. HAVING Count(StuName)=2 AND A.CourseName="企业管理"  OR A.CourseName="马克思"

复制代码