论坛 › Excel 数据透视表中级班 › 数据透视表中级班练习四

JLxiangwei 发表于 2014-5-16 09:48

数据透视表中级班练习四

本帖最后由 JLxiangwei 于 2014-6-3 18:26 编辑



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

午夜洗衣机 发表于 2014-5-16 15:57

本帖最后由 午夜洗衣机 于 2014-5-16 17:10 编辑

交作业:

方法一(字符数236个):select a.StuId,b.StuName
from (select StuId
from
where CourseId in(select Courseid from where coursename in("马克思","企业管理"))
group by StuId,CourseId)a,
b
where a.StuId=b.StuId
group by a.StuId,b.StuName
having count(a.StuId)>1这次的代码套了好几层,我的代码写得比较啰嗦:

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

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


2.select StuId from where CourseId in(select Courseid from 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,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个,比方法一多...):select a.StuId,b.StuName from a
left join
b
on a.StuId=b.StuId
where CourseId in(select Courseid from where coursename ="马克思") and a.StuId in(select StuId from
where CourseId in(select Courseid from where coursename ="企业管理"))方法三(字符数191个,最少,而且没有子查询):select a.StuId,c.StuName
from a,b,c
where a.CourseId=b.CourseId and a.StuId=c.StuId and b.CourseName in("马克思","企业管理")
group by a.StuId,c.StuName
having(count(a.StuId)=2)
把a,b,c 三个表连接起来,取课程名是马克思和企业管理的学生ID和姓名
关键是这一句:having(count(a.StuId)=2),筛选同时选择两门课的学生

xdragon 发表于 2014-5-16 16:21

本帖最后由 xdragon 于 2014-5-16 16:42 编辑

select a.StuId,a.StuName from a,b,c where
a.StuId=b.StuId and c.CourseId=b.CourseId and CourseName in ('企业管理','马克思') group
by a.StuId,a.StuName having count(a.StuId)=2

云影 发表于 2014-5-16 23:09

本帖最后由 云影 于 2014-5-17 16:30 编辑

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

jio1ye 发表于 2014-5-17 00:12

我就只会导入外部数据处理了。

天空的雨 发表于 2014-5-17 11:20

无影无形 发表于 2014-5-18 19:55

本帖最后由 无影无形 于 2014-5-30 11:38 编辑

select distinct a.stuid,b.stuname from a,b where   a.stuid=b.stuid and a.courseid in("001","002")思路有点混乱，敬请老师指点。select d.stuid,stuname, count(*) as stuid from (select * from d left outer join (select a.courseid,a.coursename,b.stuid,b.courseid   from a   left outer join b   on a.courseid=b.courseid where coursename="马克思" or coursename="企业管理")cond.stuid=c.stuid) where coursename<>"" group by d.stuid,stuname having count(*)>1不用子查询不会做count>1的步骤select c.stuid,c.stuname ,count(*) asstuid from(select a.coursename,c.stuid,c.stunamefrom a,b,c where a.courseid=b.courseid and b.stuid=c.stuid anda.coursename in("马克思","企业管理") order by c.stuid ) group by c.stuid,c.stuname having count(*)>1

箫风 发表于 2014-5-18 21:58

**** Hidden Message *****

lasharks 发表于 2014-5-19 21:21

本帖最后由 lasharks 于 2014-5-19 21:37 编辑

先上一个，回头在想其他的select a.StuId,b.StuName from (select count(*) as ct,stuid from (select a.stuid,b.coursename from a,b where a.courseid=b.courseid and (b.coursename="马克思" or b.coursename="企业管理")) group by stuid)a,b where a.ct=2 and a.stuid=b.stuid不用子查询了，感觉省了点字符select a.stuid,c.StuName from 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

车仁静 发表于 2014-5-20 14:51

SELECT B.StuId, StuName
FROM A,B,C
WHERE A.CourseId = B.CourseId AND B.StuId = C.StuId
GROUP BY A.CourseId, A.CourseName, B.StuId, B.CourseId, C.StuId,C.StuName
HAVING Count(StuName)=2 AND A.CourseName="企业管理"OR A.CourseName="马克思"

查看完整版本: 数据透视表中级班练习四