SELECT * FROM TB T WHERE NOT EXISTS(SELECT * FROM TB WHERE NAME=T.NAME AND SCORE<80)
select name from tb group by name having(avg(score)>80)
select name from stuscore where score>=80 group by name having count(name)=(select count(distinct subject) from stuscore)
---如果张三缺考“物理”呢? insert into stuscore(name, subject, score) select '张三','数学',89 UNION ALL SELECT '张三','语文',80 UNION ALL SELECT '张三','英语',90 UNION ALL SELECT '李四','数学',90 UNION ALL SELECT '李四','语文',70 UNION ALL SELECT '李四','英语',80 UNION ALL SELECT '李四','物理',80;select name, subject, score from stuscore t1 where name in (select name from stuscore );select name from stuscore where score>=80 group by name having count(name)=(select count(distinct subject) from stuscore)SELECT * FROM stuscore T WHERE NOT EXISTS(SELECT * FROM stuscore WHERE NAME=T.NAME AND SCORE<80)
SELECT * FROM TB WHERE name not in(SELECT name FROM TB WHERE SCORE<80)
select name from tb group by name having(avg(score)>80)
from stuscore
where score>=80
group by name
having count(name)=(select count(distinct subject) from stuscore)
---如果张三缺考“物理”呢?
insert into stuscore(name, subject, score)
select
'张三','数学',89 UNION ALL SELECT
'张三','语文',80 UNION ALL SELECT
'张三','英语',90 UNION ALL SELECT
'李四','数学',90 UNION ALL SELECT
'李四','语文',70 UNION ALL SELECT
'李四','英语',80 UNION ALL SELECT
'李四','物理',80;select name, subject, score
from stuscore t1
where name in (select name from stuscore );select name
from stuscore
where score>=80
group by name
having count(name)=(select count(distinct subject) from stuscore)SELECT * FROM stuscore T WHERE NOT EXISTS(SELECT * FROM stuscore WHERE NAME=T.NAME AND SCORE<80)