1.union
select name from tb1 where score=(
SELECT max(score) from tb1
)
union
select name from tb1 where score=(
SELECT min(score) from tb1
)
2.两门分数加起来的第2-5名,group by
select name, count(score),sum(score) from tb1
group by name
HAVING count(score)=2
ORDER BY
sum(score) desc
limit 1,4;
3.两门加起来低于 150,having分组过滤条件
select name ,sum(score) from tb1 group by name
having count(score)=2
and sum(score)<150
4.两门平均分在60-80,having分组过滤条件+avg()平均
select name ,avg(score) from tb1 group by name
having count(score)=2
and avg(score)>=60 and avg(score)<=80;
5.总分大于150,平均分小于90的人数
#总分大于150,平均分小于90的人数
select count(*)as '符合人数'
from
(select id from tb1
group by name
HAVING sum(score)>100 and avg(score)<90
) a
6.看不懂,语文不好…