爱做数据的小女孩

分类

题解(129)

/ 注册

全部文章（共98篇）

题解 | #牛牛学说话之-整数#

a=int(input())print(a)

2023-09-16

0 284

题解 | #计算商城中2021年每月的GMV#

# 已付款订单：status=1 # 未付款的订单:status=0 # gmv:已付款+未付款==status=1and status=0 # where status!=2 select date_format(event_time,"%Y-%m") as month, round(sum(t...

2023-03-01

0 412

题解 | #2021年11月每天的人均浏览文章时长#

# 个人认为最难的地方在于timestampdiff时间函数的使用，一般是很难想得到用它解题思路：2021年11月每天的人均浏览时长 1，分组依据是时间，用到date_format函数，人均浏览时长，需要用到sum进行计算，然后用总秒数除总人数得人均。2，需要剔除重复人头数，条件过滤中需要用到art...

2023-02-28

1 343

题解 | #修复串列了的记录#

select exam_id, substring_index(tag,',',1) as tag, substring_index(substring_index(tag,',',2),',',-1) as difficulty, substring_index(tag,',',-1) as d...

2023-02-22

0 337

题解 | #对过长的昵称截取处理#

方式一： # select # uid, # if(char_length(nick_name)>13,concat(substring(nick_name,1,10),'...'),nick_name) # from # user_info # where char_length(n...

2023-02-22

0 384

题解 | #每类试卷得分前3名#

select * from(select tag, uid, ROW_NUMBER() over (partition by e.tag order by max(r.score)desc,min(score)desc,uid desc) as ranking from examinatio...

2023-02-21

0 447

题解 | #每份试卷每月作答数和截止当月的作答总数。#

select exam_id, date_format(start_time,"%Y%m") as start_month, count(start_time) as month_cnt, sum(count(start_time)) over (partition by exam_id orde...

2023-02-21

0 332

题解 | #0级用户高难度试卷的平均用时和平均得分#

select u.uid as uid, round(avg(if(r.score is null,0,r.score)),0) as avg_score, round(avg(if(r.submit_time is null,duration,TIMESTAMPDIFF(minute,start...

2023-02-21

0 350

题解 | #试卷发布当天作答人数和平均分#

select i.exam_id,count(distinct u.uid) as uv,round(avg(e.score),1) as avg_score from user_info as u right join exam_record as e on u.uid=e.uid r...

2023-02-20

0 385

题解 | #插入记录（一）#

方式一 # insert into exam_record(uid,exam_id,start_time,submit_time,score) # values(1001,9001,'2021-09-01 22:11:12','2021-09-01 23:01:12',90), # (1002,9...

2023-01-19

0 309