牛客666375956号

题解

/ 注册

全部文章 / 题解（共51篇）

题解 | #每个6/7级用户活跃情况#

思路很简单，但做的有点复杂了，我不太清楚怎么在分组中运用limit选取前3名 SELECT tag,uid,ranking from(SELECT tag,uid,ROW_NUMBER()over(PARTITION BY tag order by tag, max_value desc,min_v...

Mysql

2022-01-22

0 219

题解 | #每个6/7级用户活跃情况#

思路，分成两个部分，union all 一个表，再join一个表 select f.uid,f.act_month_total,f.act_days_2021,g.act_days_2021_exam,g.act_days_2021_question from (select a.uid,COUN...

Mysql

2022-01-20

0 201

题解 | #满足条件的用户的试卷完成数和题目练习数#

SELECT a.uid, COUNT(distinct case when year(r.submit_time)=2021 then r.id else null end)as exam_cnt, COUNT(distinct case when year(p.submit_time)=2021...

Mysql

2022-01-20

0 217

题解 | #作答试卷得分大于过80的人的用户等级分布#

考虑连接，其中有个细节，当数量相同时，要对等级进行降序排序 select `level`,count(distinct uid)as level_cnt from examination_info e inner join (select i.level,r.uid,r.score,r.exam_...

Mysql

2022-01-18

0 237

题解 | #得分不小于平均分的最低分#

union all 的使用 SELECT DATE_FORMAT(submit_time, "%Y%m")AS submit_month, COUNT(submit_time) AS month_q_cnt, ROUND(COUNT(submit_time)/MAX(DAY(last...

Mysql

2022-01-18

0 292

题解 | #得分不小于平均分的最低分#

select min(score)as min_score_over_avg from exam_record r left join examination_info i on r.exam_id=i.exam_id where i.tag="SQL" and score>=(select ...

Mysql

2022-01-17

0 193

题解 | #统计作答次数#

select count(start_time)as total_pv,sum(case when submit_time is not null and score is not null then 1 else 0 end)as complete_pv, count(distinct exam_...

Mysql

2022-01-17

0 249

题解 | #更新记录（一）#

select i.tag,i.difficulty,round((sum(r.score)-max(r.score)-min(r.score))/(count(score)-2),1) as clip_avg_score from examination_info i right JOIN exam...

Mysql

2022-01-17

0 222

题解 | #浙大不同难度题目的正确率#

先将三个表连接起来，然后采用case when 语句进行正确率计算 from(SELECT device_id,university from user_profile) a left join (select p.device_id,p.result,q.difficult_level from...

Mysql

2022-01-16

0 219

题解 | #商品交易(网易校招笔试真题)#

采用内连接 select a.id,a.name,a.weight,a.total from (select g.id,g.name,g.weight,sum(t.count) as total from goods g right join trans t on g.id=t.goods_id g...

Mysql

2021-12-25

0 219