sql查询问题

[mr_zzc]

table stu_info(s_id,s_name)
table stu_score(s_id,c_id)
table course(c_id,c_name)
查询学生中每门课程成绩高于80分的学生，列出学号，姓名，课程名，成绩
注：是每一门课程成绩都高于80分
谢谢各位了..

[claro]

select a.s_id 学号,a.s_name 姓名,c.c_name 课程名，max(成绩)
from stu_info a
join stu_score b on a.s_id=b.s_id
join course c on b.c_id=c.c_id
where b.成绩 > 80
group a.s_id,a.s_name,c.c_name

[--小F--]

select
   a.s_id,a.s_name,c.c_name,b.成绩
from
   stu_info a,stu_score b,course c
where
   a.s_id=b.s_id 
and
   b.c_id=c.c_id
and
   b.成绩>80

[--小F--]

成绩字段都没看见

[fpzgm]

select a.s_id,a.s_name,c.c_name,b.成绩
from stu_info a
left join stu_score b on a.s_id=b.s_id
left join course c on b.c_id=c.c_id
where a.s_id in
(select s_id from stu_score group by s_id having min(成绩)>80)

[abuying]

table stu_score(s_id,c_id)
只有学生，课程，怎么没有成绩这一字段？
请问学生可以修多门课程吗？

[我是小数位]

[Quote=引用楼主 mr_zzc 的回复:]
table stu_info(s_id,s_name)
table stu_score(s_id,c_id)
table course(c_id,c_name)
查询学生中每门课程成绩高于80分的学生，列出学号，姓名，课程名，成绩
注：是每一门课程成绩都高于80分
谢谢各位了..
[/Quote]沒有成績這一列

[abuying]

select * from stu_score where not exist(select 1 from stu_score t where stu_score.s_id=t.s_id and stu_score.c_id!=t.c_id and t.score<80)
--
select a.s_id,a.s_name,c.c_name,b.score
       from stu_info a inner join
        (select * from stu_score where not exist(select 1 from stu_score t where stu_score.s_id=t.s_id and stu_score.c_id!=t.c_id and t.score<80)) as b
        on a.s_id=b.s_id
        inner join course c on b.c_id=c.c_id

[abuying]

create table stu_info(s_id varchar(10),s_name varchar(20))
create table stu_score(s_id varchar(10),c_id int,score int)
create table course(c_id int,c_name varchar(20))
insert into stu_info
select '10001','jake'
union select '10002','tony'

insert into course
select 1,'C语言'
union select 2,'计算机系统概要'
union select 3,'数据结构'
union select 4,'高等数学'

insert into stu_score
select '10001',1,100
union select '10001',2,90
union select '10001',3,85
union select '10001',4,80
union select '10002',1,75
union select '10002',2,80
union select '10002',3,80
union select '10002',4,80

GO
select * from
(select s_id,count(s_id) as icount1 from stu_score group by s_id) as s
inner join
(select s_id,count(s_id) as icount2 from stu_score where not exists(select 1 from stu_score t where stu_score.s_id=t.s_id and stu_score.c_id=t.c_id and t.score<80)
group by s_id) as u
on s.s_id=u.s_id and s.icount1=u.icount2
--这里得到每门课大于80人的学号

select a.s_id,a.s_name,c.c_name,sc.score
from stu_info a inner join
(select s.s_id from
(select s_id,count(s_id) as icount1 from stu_score group by s_id) as s
inner join
(select s_id,count(s_id) as icount2 from stu_score where not exists(select 1 from stu_score t where stu_score.s_id=t.s_id and stu_score.c_id=t.c_id and t.score<80)
group by s_id) as u
on s.s_id=u.s_id and s.icount1=u.icount2) n
on a.s_id=n.s_id
inner join stu_score sc
on n.s_id=sc.s_id and a.s_id=sc.s_id
inner join course c on sc.c_id=c.c_id


s_id s_name c_name score
---------- -------------------- -------------------- -----------
10001 jake C语言 100
10001 jake 计算机系统概要 90
10001 jake 数据结构 85
10001 jake 高等数学 80

(4 行受影响)

[abuying]

忘 了加SQL code

create table stu_info(s_id varchar(10),s_name varchar(20))
create table stu_score(s_id varchar(10),c_id int,score int)
create table course(c_id int,c_name varchar(20))
insert into stu_info  
select '10001','jake'
union select '10002','tony'

insert into course
select 1,'C语言'
union select 2,'计算机系统概要'
union select 3,'数据结构'
union select 4,'高等数学'

insert into stu_score  
select '10001',1,100
union select '10001',2,90
union select '10001',3,85
union select '10001',4,80
union select '10002',1,75
union select '10002',2,80
union select '10002',3,80
union select '10002',4,80

GO
select * from  
(select s_id,count(s_id) as icount1 from stu_score group by s_id) as s
inner join
(select s_id,count(s_id) as icount2 from stu_score where not exists(select 1 from stu_score t where stu_score.s_id=t.s_id and stu_score.c_id=t.c_id and t.score<80)
group by s_id) as u
on s.s_id=u.s_id and s.icount1=u.icount2
--这里得到每门课大于80人的学号

select a.s_id,a.s_name,c.c_name,sc.score  
  from stu_info a inner join
  (select s.s_id from  
  (select s_id,count(s_id) as icount1 from stu_score group by s_id) as s
  inner join
  (select s_id,count(s_id) as icount2 from stu_score where not exists(select 1 from stu_score t where stu_score.s_id=t.s_id and stu_score.c_id=t.c_id and t.score<80)
  group by s_id) as u
  on s.s_id=u.s_id and s.icount1=u.icount2) n
  on a.s_id=n.s_id
  inner join stu_score sc
  on n.s_id=sc.s_id and a.s_id=sc.s_id
  inner join course c on sc.c_id=c.c_id


s_id s_name c_name score
---------- -------------------- -------------------- -----------
10001 jake C语言 100
10001 jake 计算机系统概要 90
10001 jake 数据结构 85
10001 jake 高等数学 80

(4 行受影响)