mysql-小题型集

1.总成绩大于150分，且数学大于等于60，且年龄大于等于20岁的学生的平均成绩是多少？

cid name age sex project score
12 张三 25 男 chinese 50
12 张三 25 男 math 20
12 张三 25 男 english 70
12 李四 20 男 chinese 50
12 李四 20 男 math 50
12 李四 20 男 english 50
12 王芳 19 女 chinese 70
12 王芳 19 女 math 70
12 王芳 19 女 english 70
13 张大三 25 男 chinese 60
13 张大三 25 男 math 60
13 张大三 25 男 english 70
13 李大四 20 男 chinese 50
13 李大四 20 男 math 60
13 李大四 20 男 english 50
13 王小芳 19 女 chinese 70
13 王小芳 19 女 math 80
13 王小芳 19 女 english 70

方法一：

思路：先找出总成绩大于150分人名，再找出且数学大于等于60，且年龄大于等于20岁的人名，join出人名，再求出平均成绩

select 
s1.name,round(avg(score),2)
from student s1
right join (
	select t1.name 
	from(
		SELECT 
		name
		from student 
		group by name 
		HAVING sum(score )>150
	)t1
	join
	(
		select name
		from student 
		where project ='math' and score>=60 and age >=20
	)t2
	on t1.name=t2.name
)s2
on s1.name=s2.name
group  by s1.name

方法二：

总成绩大于150分，且数学大于等于60，且年龄大于等于20岁的每个学生的平均成绩是多少？

select t.name,round(t.avg_score,2)
from 
( 
	select score,project,name,age, 
		avg(score) over (PARTITION by name )avg_score,
		sum(score)over (PARTITION by name ) sum_score
	from  student
) as t 
where t.sum_score > 150 and t.project = 'math' and t.score >= 60 and t.age >= 20 

方法二：

总成绩大于150分，且数学大于等于60，且年龄大于等于20岁的所有学生的平均成绩是多少？

select t.name,round(t.avg_score,2)
from 
( 
	select score,project,name,age, 
		avg(score) over ()avg_score,
		sum(score)over () sum_score
	from  student
) as t 
where t.sum_score > 150 and t.project = 'math' and t.score >= 60 and t.age >= 20 

2 .有50W个京东店铺，每个顾客访客访问任何一个店铺的任何一个商品时都会产生一条访问日志，访问日志存储的表名为Visit，访客的用户id为user_id，被访问的店铺名称为shop，请统计：

数据集

用户Id  店铺名称
u1     a
u2     b
u1     b
u1     a
u3     c

1）每个店铺的UV（访客数）

店铺名称 uv值

select shop ,count(shop)
from  visit
group by shop

2）每个店铺访问次数top3的访客信息。输出店铺名称、访客id、访问次数

店铺名称  用户编号  访问次数
a        u1         10
a        u3         8
a        u6         7
b        xx         xx
b        xx         xx
b        xx         xx

select shop  ,user_id  ,  count
from(
		select shop  ,user_id  ,  count ,row_number()over(partition by shop order by count desc)  c
		from(
				select
			    shop  ,user_id  ,count(*) count
				from  visit
				group by user_id,shop
		)t1 
)t2 where t2.c<=3

3.我们有如下的用户访问数据，要求使用SQL统计出每个用户的累计访问次数，如下所示：：

数据集

userID visitDate visitCount
u01 2017/1/21 5
u02 2017/1/23 6
u03 2017/1/22 8
u04 2017/1/20 3
u01 2017/1/23 6
u01 2017/2/21 8
u02 2017/1/23 6
u01 2017/2/22 4

结果如下所示

用户 月份 小计 累计
u01 2017-01 11 11
u01 2017-02 12 23
u02 2017-01 12 12
u03 2017-01 8 8
u04 2017-01 3 3

解释：小计为单月访问次数，累计为在原有单月访问次数基础上累加，将计算结果写入到mysql的表中，自己设计对应的表结构

SELECT
t1.userid,t1.c1,t1.c2 ,sum(t1.c2)over(partition by t1.userid order by t1.c1)
from(
select 
userid,date_format(date(vdate),"%Y-%m") c1,sum(vcount)c2
from visit2
group by userid,date_format(date(vdate),"%Y-%m")
)t1

求出每辆车每天上传N条数据，要求获得每日最后一条数据，请用至少3种方式写出来

vin,pkgts,value
豫A11111,2022-06-01 16:54:57,60.0
豫A11111,2022-06-01 07:40:27, 6.0
豫A11111,2022-06-26 12:16:45,64.0
豫A11112,2022-06-05 23:00:42,18.0
豫A11112,2022-06-05 09:10:17,44.0
豫A11112,2022-06-05 13:00:25,19.0
豫A11112,2022-06-13 16:14:04,43.0
豫A11112,2022-06-13 07:21:08,40.0

-- 方法一
select 
vin,pkgts,value
from(select 
	vin,pkgts,value,rank()over(partition by vin,date(pkgts) order by pkgts desc) rk
	from  nt2
	)t1
where rk=1

-- 方法二
select 
vin,pkgts,value
from(select 
	vin,pkgts,value,lead(pkgts)over(partition by vin,date(pkgts) order by pkgts ) rk
	from  nt2
	)t1
where rk is NULL 

-- 方法三
select 
nt2.*
from nt2 
join (select 
		vin,max(pkgts)c1
		from nt2 
		group by vin, date(pkgts)
		)t1
on t1.c1=nt2.pkgts and t1.vin=nt2.vin 

-- 方法四
select 
vin,pkgts,value
from (
      select vin,pkgts,value,max(pkgts) over(partition by vin,date(pkgts)   ) rk
      from  nt2)t1
where t1.pkgts=t1.rk