create table test2( id int not null auto_increment, name varchar(10), kecheng varchar(10), fenshu int, primary key(id) ) insert into test2(name,kecheng,fenshu) values('張三','語(yǔ)文',81 ); insert into test2(name,kecheng,fenshu) values('張三','數(shù)學(xué)',75 ); insert into test2(name,kecheng,fenshu) values('李四','語(yǔ)文',76 ); insert into test2(name,kecheng,fenshu) values('李四','數(shù)學(xué)',90 ); insert into test2(name,kecheng,fenshu) values('王五','語(yǔ)文',81 ); insert into test2(name,kecheng,fenshu) values('王五','數(shù)學(xué)',100); insert into test2(name,kecheng,fenshu) values('王五','英語(yǔ)',90 );
-- (1) select name from test2 group by name having min(fenshu) > 80; 王五
-- (2) select distinct name from test2 where name not in (select distinct name from test2 where fenshu<=80)
