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('張三','語文',81 ); insert into test2(name,kecheng,fenshu) values('張三','數學',75 ); insert into test2(name,kecheng,fenshu) values('李四','語文',76 ); insert into test2(name,kecheng,fenshu) values('李四','數學',90 ); insert into test2(name,kecheng,fenshu) values('王五','語文',81 ); insert into test2(name,kecheng,fenshu) values('王五','數學',100); insert into test2(name,kecheng,fenshu) values('王五','英語',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)
