mysql相关

最新推荐文章于 2022-02-21 23:36:35 发布

java学瓜呆小

最新推荐文章于 2022-02-21 23:36:35 发布

阅读量80

点赞数

分类专栏： java常见面试题

本文链接：https://blog.csdn.net/qq_45394274/article/details/104926738

版权

13 篇文章 1 订阅

订阅专栏

mysql
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.看不懂,语文不好…

关注