mysql分组提取特定组所有数据

Question

需求：

1、满足第一次数学排名在前十

2、满足第二次英语排名在前十

3、满足第三次物理排名在前十

4、科目成绩80以上

5、科目成绩80以上有两科的学生

6、查询满足以上条件的学生的那几科成绩

目标结果：

详细描述：

以上条件必须都满足

Answer 1

总感觉这题目有点问题。。凑合做出你想要的结果。。应该有更优的sql

select * from test_information 
where id in(select id from test_information where score > 80 and rank1 <= 10 and rank2 <= 10 and rank3 <= 10 group by id  )
and name in (select name from test_information where score > 80 and rank1 <= 10 and rank2 <= 10 and rank3 <= 10 group by name having count(*) > 1 )
order by name