select distinct name from cj t1 where (select count(*) from cj where name=t1.name and chengji>=80)=3
select distinct name from cj minus select distinct name from cj where chengji<80
1. 所有客都>80分就是不存在<80分的课 select distinct name from cj t1 where not exists ( select 1 from cj t2 where t1.name=t2.name and t2.chengji<80);2. 用表关联 select distinct t1.name from cj t1, cj t2, cj t3 where t1.name=t2.name and t1.name=t3.name and t1.xuke='语文' and t1.chengji>80 and t2.xuke='数学' and t2.chengji>80 and t3.xuke='语文' and t3.chengji>80
cenlmmx(学海无涯苦作舟 ) 高手呀。
select * from ( select name,sum(decode(xueke,'语文',chengji,0)) as yuwen, sum(decode(xueke,'数学',chengji,0)) as shuxue, sum(decode(xueke,'英语',chengji,0)) as yingyu from cj group by name) where yuwen>80 and shuxue>80 and yingyu>80
cenlmmx(学海无涯苦作舟) 高 实在是高
先找到各门成绩最低分高于80人的名字就可以了呀! select name from cj group by name having min(chengji) >80
from cj t1
where (select count(*) from cj where name=t1.name and chengji>=80)=3
minus
select distinct name from cj where chengji<80
select distinct name from cj t1 where not exists (
select 1 from cj t2 where t1.name=t2.name and t2.chengji<80);2. 用表关联
select distinct t1.name from cj t1, cj t2, cj t3
where t1.name=t2.name and t1.name=t3.name and t1.xuke='语文' and t1.chengji>80
and t2.xuke='数学' and t2.chengji>80 and t3.xuke='语文' and t3.chengji>80
高手呀。
select name,sum(decode(xueke,'语文',chengji,0)) as yuwen,
sum(decode(xueke,'数学',chengji,0)) as shuxue,
sum(decode(xueke,'英语',chengji,0)) as yingyu
from cj
group by name)
where yuwen>80 and shuxue>80 and yingyu>80
高 实在是高
select name
from cj
group by name
having min(chengji) >80