烛少

题解

/ 注册

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

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

因为只用一个查询时同时使用distinct和order by会报错，所以再套一层子查询 select music_name from (select distinct ml.music_id,m.music_name from follow f left join music_likes ml on...

Mysql

2022-02-07

0 278

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

笨办法，将中位数和每个grade对应的排名区间列出来，中位数在排名区间内选中即可 with total as (select sum(number) tnum from class_grade) select grade from ( select * ,sum(number) over(order...

Mysql

2022-02-07

0 423

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

本题比较有意思，方法思路非常多 1、巧秒方法：在group_concat(date)中找用户最找登录日期的下一天，如果能找到，那就标记为1，否则为0 select round(sum(t)/count(*),3) from (select user_id ,if( FIND_IN_SET(date...

Mysql

2022-02-06

18 654

题解 | #异常的邮件概率#

select date,round(sum(type='no_completed')/count(*),3) p from email e,user u1,user u2 where send_id=u1.id and receive_id=u2.id and u1.is_blacklist=0 a...

Mysql

2022-02-06

0 354

题解 | #将employees表中的所有员工的last_name和first_name通过引号连接起来。#

select concat(last_name,"'",first_name) name from employees * 一次性的更改 alter table employees drop column name; alter table employees add column name var...

Mysql

2022-02-06

0 375

题解 | #查找在职员工自入职以来的薪水涨幅情况#

select distinct emp_no ,(first_value(salary) over w - last_value(salary) over w) as growth from salaries s where emp_no in (select emp_no from salarie...

Mysql

2022-02-06

0 235

题解 | #查找在职员工自入职以来的薪水涨幅情况#

select后使用子查询即可 select e.emp_no, ( (select s2.salary from salaries s2 where s2.emp_no=e.emp_no and s2.to_date='9999-01-01') - (select s1.sa...

Mysql

2022-02-06

0 235

题解 | #获取当前薪水第二多的员工的emp_no以及其对应的薪水salary#

select e.emp_no,s3.salary,last_name,first_name from employees e join salaries s3 on e.emp_no=s3.emp_no where (select count(distinct s1.salary) from s...

Mysql

2022-02-06

0 258

题解 | #获取当前薪水第二多的员工的emp_no以及其对应的薪水salary#

子查询最简便的写法 select emp_no,salary from salaries s where (select count(distinct s1.salary) from salaries s1 where s.salary<=s1.salary )=2

Mysql

2022-02-06

0 213

题解 | #近一个月发布的视频中热度最高的top3视频#

select t1.video_id ,round( ((sum(case when timestampdiff(second,start_time,end_time)>=duration then 1 else 0 end ))/count(*)*100 +sum(if_like)*5 +c...

Mysql

2022-01-23

9 1080