如何查询出每门课程都大于八十分的人的姓名

[wyf2]

有一张students表

name kechen fenshu

张三 语文 82
李四 英语 65
张三 数学 87
李四 数学 90
张三 英语 81


现在要显示出每门课程都大于80分的学生
请问如何实现

[zghua851004]

select name from student
where fenshu >80

[zghua851004]

呵呵没看清题目
写错了

[kk19840210]

create table #tt (name varchar(10),kechen varchar(10),fenshu int)

insert into #tt values('张三','语文',82)
insert into #tt values('张三','英语',81)
insert into #tt values('张三','数学',87)
insert into #tt values('李四','英语',65)
insert into #tt values('李四','数学',90)


select distinct name from #tt a where (select count(1) from #tt where name=a.name)=(select count(1) from #tt where name=a.name and fenshu>=80)


name
----------
张三

(1 行受影响)

[worldly_wind]

select *
from students
where fenshu > 80
and name not in (select name from students where fenshu < 80);

[$扫地僧$]

create table students
( 
   name varchar(10),
   kechen  varchar(10),
   fenshu int
)

insert students select '张三','语文',82 
insert students select '李四','英语',65 
insert students select '张三','数学',87 
insert students select '李四','数学',90 
insert students select '张三','英语',81



select * 
from students A
where name not in  (select name from students where fenshu<80)

[wyf2]

count(1)是什么意思?
那如果学生有一百多个那不是要写一条inset into

[$扫地僧$]

--or
select * 
from students A
where not exists (select 1 from students where A.name=name and fenshu<80)

[wyf2]

琳·风の狼(修罗):你写的不就把李四那显示出来了,李四的数学也大于80但英语小于80的
再问一个,怎么把这张表改成这样

姓名 语文 数学 英语
张三 82 87 81
李四 90 65

[wyf2]

A.name=name有什么区别呀,有都是同一张表吗

[yangjiexi]

select * 
from #students A
where name not in  (select name from #students where fenshu<80)


select name,sum(case when kechen=N'语文' then fenshu else null end) '语文',
            sum(case when kechen=N'数学' then fenshu else null end) '数学',
	    sum(case when kechen=N'英语' then fenshu else null end) '英语'
from #students
group by name

[wyf2]

谢谢
最后问下
select *
from #students A
where name not in (select name from #students where fenshu<80)
什么意思呀,不懂#students和A不都是同一张表吗

[yangjiexi]

select *
from #students
where name not in (select name from #students where fenshu <80)

这样就可以,A是#students的别名

[yangjiexi]

别名是为了引用的时候方便,比如有2个表都有ID这个字段,
给这几个别分别设别名a,b,就可以用a.ID,b.ID,
而不用写表名,就是用a,b代替表名

[wyf2]

那楼的那位用A.name=name 这个什么意思吗,A.name和name有区别吗

[yangjiexi]

select *
from students A
where not exists (select 1 from students where A.name=name and fenshu<80)

select *
from students A
where not exists (select 1 from students B
where A.name=B.name and B.fenshu<80)

[yangjiexi]

LZ不懂就用
select *
from #students
where name not in(select name from #students where fenshu<80)

[sunrisehy2003]

超簡單語句
select name from students
where fenshu>80
group by name
having count(kechen)=3
說明,如只有三門課程,上面就是3,如有四門課,就寫成aving count(kechen)=4,依此類推

[chen_gongyu]

是的有區別，暫時可以看成兩個表

[wyf2]

谢谢各位了

[wcj1018_net]

SELECT *
FROM aa a
WHERE NOT EXISTS (SELECT 1 FROM aa WHERE a.name=NAME AND COUNT<80)