又菜又爱玩^

题解

/ 注册

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

题解 | #今天的刷题量(一)#

-- 看着简简单单但是我写的很复杂 WITH subnew AS ( SELECT submission.id,subject_id,create_time,name FROM submission LEFT JOIN subject s ON s...

Mysql

2022-03-08

0 318

题解 | #网易云音乐推荐(网易校招笔试真题)#

分解分解 YYDS SELECT t1.music_name FROM ( WITH follow_new AS ( SELECT follower_id FROM follow WHERE user...

Mysql

2022-02-22

0 266

题解 | #获得积分最多的人(三)#

sum 开窗 + if判断 WITH grade_new AS ( SELECT *,sum(if(gi.type ='reduce',gi.grade_num * -1,gi.grade_num)) OVER (PARTITION BY name) rk FROM user ...

Mysql

2022-02-22

0 230

题解 | #获得积分最多的人(二)#

89和90 解题方法差不多吧，有点迷惑？？？？ WITH grade_new AS ( SELECT *, sum(gi.grade_num) OVER (PARTITION BY name) rk FROM user LEFT JOIN grade_info gi O...

Mysql

2022-02-22

0 174

题解 | #牛客的课程订单分析(七)#

可以参考一下。 WITH order_new AS ( SELECT * FROM ( SELECT *, count(*) OVER (PARTITION BY user_id) rk FROM order_info o ...

Mysql

2022-02-21

0 219

题解 | #牛客的课程订单分析(五)#

感觉有点繁琐了 SELECT t2.user_id,t2.lagdate first_buy_date,t2.date second_buy_date,t2.rk cnt FROM ( SELECT *, row_number() OVER (PARTITION BY user_id) rk2 F...

Mysql

2022-02-18

1 180

题解 | #牛客每个人最近的登录日期(五)#

会笛卡尔乘积 select t1.day1, ifnull(round(t1.incre_user / (t1.new_user /2 ),3),0.00) p from ( select sum(case when a.tmp=1 then 1 else 0...

Mysql

2021-09-27

0 329

题解 | #出现三次以上相同积分的情况#

where 竟然可以并列判断哈哈哈学到了 select t1.date, round(no_completed/count_completed,3) p from ( select date, count(*) count_comp...

Mysql

2021-09-24

0 302

题解 | #出现三次以上相同积分的情况#

窗口函数永远滴神！ select t1.id, t1.job, score, t_rank from ( select id, job, score, dense_rank() over (partition by job order by score desc ) t_ra...

2021-08-17

0 313

题解 | #出现三次以上相同积分的情况#

窗口 + 内连接 select t1.id, t1.job, score, t_rank from ( select id, job, score, dense_rank() over (partition by job order by score desc ) t_ran...

2021-08-17

0 263