菜鸟求助~关于多表连接与group by的问题

[bjfuwyp]

比较长哈,请各位大大耐心看多谢~
现在有三张表
Student(ID,Name,....)
Course(CourseID,CourseName,Teacher,...)
SC(ID,CourseID,Score)
题目要求:查询有两门及两门以上课程成绩(Score)在85分以下(<=85)的学生学号，姓名，所选课程及相应的分数。

老师说这道题可以用4,5种方法实现,我想了一个晚上只想到2种,所以来请教各位大大~

该题要select三张表的属性所以要用到表连接,同时要用到聚合函数count计数所以还要用到group by..
但是在mssql2000下多表连接时group by不太好使,所以我的主要思路是多表连接后作为一个单表嵌套进查询块然后用group by,或者先group by一张单表再用表连接.

我已用两种方法实现查询如下:方法1,这个方法是最容易想到的,如下

select SC.ID,Name,Coursename,Score
from Student,SC,Course
where Student.ID=SC.ID
and SC.CourseID=Course.CourseID
and SC.ID in 
(select ID  
From SC
where Score<=85
group by ID
having count(*)>=2)
and Score<=85

方法2:这个是将单表group by之后作为一张新表和其他表连接..

select css1.ID,Name,CourseName,Score 
from Student,SC,Course,
(select ID 
from SC 
where Score<=85 
group by ID 
having count(*)>1)as css1,
where Student.ID=SC.ID
and SC.ID=css1.ID
and CourseID=SC.CourseID
and Score<=85

主要问题有以下几个
1.单纯对于group by来说,若执行select * from SC group by ID having count(*)>1,会对ID分组并选出选了2门课以上的ID,但是查询结果中每个ID只有一个选课记录,省去了这个ID的其他选课记录,我若想要选出来的ID的所有选课记录则必须这样写语句:select * from SC where ID in (select * from SC group by ID having count(*)>1),这是不是有些麻烦?有没有其他可以等效的语句呢?

2.有什么语句可以替代select * from SC group by ID having count(*)>1呢?就是完全不用group by来实现分组功能?

3.对于多表连接并且select多张表中属性的情况如何用子查询块替代where Student.ID=SC.ID AND Course.CourseID=SC.CourseID呢?我知道很典型的子查询替代表连接的例子里虽然涉及到多表,但是select后面跟着的属性是一张表里面的...所以还是很不一样的..

4.各位大大能不能就这个题目提出其他的思路来解题呢?我实在想不出来了...


为了方便测试,以下是建表语句:

create table Student(ID varchar(20),Name varchar(20),Sex varchar(20),Age int,Department varchar(20));
create table Course (CourseID varchar(20),CourseName varchar(20),Credit varchar(20),CourseBefore varchar(20));
create table SC (ID varchar(20),CourseID varchar(20),Score dec);

insert into Student values('00001',       '赵一', '男',    20,  'CS'); 
insert into Student values('00002',	'钱二',	'女',	19,	'EE');
insert into Student values('00003',	'孙三',	'男',	21,	'Design');
insert into Student values('00004',	'李四',	'男',	22,	'MATH');
insert into Student values('00005',	'周五',	'女',	20,	'IS');
insert into Student values('00006',	'吴六',	'男',	19,	'CS');
insert into Student values('00007',	'郑七',	'女',	20,	'MATH');
insert into Student values('00008',	'王九',	'男',	21,	'EE');
insert into Student values('00009',	'孙三',	'男',	20,	'Design');
insert into Student values('00010',	'周五',	'女',	19,	'IS');

insert into Course values('C1',	'计算机引论', '2',	null);
insert into Course values('C2',	'C语言',     '3',	'C1');
insert into Course values('C3',	'数据结构',  '4',	'C2');
insert into Course values('C4',	'数据库',    '3',	'C3');
insert into Course values('C5',	'信息系统',  '3',	'C4');


insert into SC values('00001',	'C1',	95);
insert into SC values('00001',	'C2',	80);
insert into SC values('00001',	'C3',	84);
insert into SC values('00002',	'C1',   80);
insert into SC values('00002',	'C2',	85);
insert into SC values('00003',	'C1',	78);
insert into SC values('00003',	'C3',	70);
insert into SC values('00005',	'C2',   75);
insert into SC values('00005',	'C5',	88);
insert into SC values('00006',	'C3',	86);
insert into SC values('00006',	'C4',   95);
insert into SC values('00009',	'C1',	82);
insert into SC values('00009',	'C2',	90);
insert into SC values('00010',	'C3',	91);

[-晴天]

select a.name,c.coursename,b.score
from student a inner join sc b on a.id=b.id
inner join course c on b.courseid=c.courseid
where exists(select 1 from sc where id=b.id and score<=85 group by id having count(*)>1)

select a.name,c.coursename,b.score
from student a inner join sc b on a.id=b.id
inner join course c on b.courseid=c.courseid
where b.id in(select id from sc where score<=85 group by id having count(*)>1)

[dawugui]

你可以参考这个帖.

http://topic.csdn.net/u/20100517/17/b2ab9d5e-73a2-4f54-a7ec-40a5eabd8621.html

[bjfuwyp]

[Quote=引用 1 楼 jinfengyiye 的回复:]

太长，友情帮顶。
[/Quote]
汗,简单来说就是怎么用5种方法实现这个题目...
查询有两门及两门以上课程成绩(Score)在85分以下(<=85)的学生学号，姓名，所选课程及相应的分数。

[--小F--]

有什么语句可以替代select * from SC group by ID having count(*)>1呢?就是完全不用group by来实现分组功能?

select * from sc t where exists(select 1 from sc where ID=t.ID and courseid<>t.courseid)

[--小F--]

3.对于多表连接并且select多张表中属性的情况如何用子查询块替代where Student.ID=SC.ID AND Course.CourseID=SC.CourseID呢?我知道很典型的子查询替代表连接的例子里虽然涉及到多表,但是select后面跟着的属性是一张表里面的...所以还是很不一样的..


还是exists

select * from student t where exists(select 1 from sc where id=t.id)