牛萍萍

题解

/ 注册

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

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

不用 in 的话，换成 rank()over() 也可 select t.u_n, t.c_n, t.date from (select ur.name u_n, cl.name c_n, ln.date, rank() over(partition by ln.user_...

Mysql

2021-10-21

0 304

题解 | #SQL54 平均工资#

也行，但好像更长了之外，效率上也没啥区别 select avg(ss.salary) from salaries ss left join (select max(salary) salary from salaries where to_date='9999-01-01' ...

Mysql

2021-10-20

0 322

题解 | #SQL24 获取所有非manager员工当前的薪水情况#

select de.dept_no, de.emp_no, ss.salary from dept_emp de # join dept_manager dm dp.emp_no=dm.emp_no join salaries ss on ss.emp_no=de.emp_no where (de....

Mysql

2021-10-18

0 356

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

select distinct m.music_name from music_likes ml join follow f on ml.user_id = f.follower_id join music m on m.id = ml.music_id where f.user_id = 1 a...

Mysql

2021-10-17

0 306

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

select g.id, g.name, g.weight, t.total from goods g left join( select goods_id, sum(count) total from trans group by goods_id having sum(count)>20...

Mysql

2021-10-16

0 363

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

select date, sum(rkd) from (select *, if(rank() over(partition by user_id order by date asc)=1, 1, 0) rkd from login) t group by date

Mysql

2021-09-27

1 286

题解 | #SQL21 对于employees表中，给出奇数行的first_name#

select first_name from (select first_name, count(emp_no) over(order by first_name) cnt from employees order by emp_no) t where cnt % 2 =1 二刷的时候，突然自己就...

Mysql

2021-09-27

12 1135

题解 | #SQL12 获取每个部门中当前员工薪水最高的相关信息#

select tt.dept_no, tt.emp_no, tt.salary from (select dp.*, ss.salary, dense_rank() over(partition by dp.dept_no order by ss.salary desc) as dr from d...

Mysql

2021-09-26

2 350

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

在职: 规定 to_date = '9999-01-01'入职：对照员工表的 hire_date涨幅：最近一次 - 入职时的薪水 select es.emp_no, (ss2.sl2-ss.salary) as growth from employees es left join salaries...

Mysql

2021-09-26

1 340

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

select * from order_info where id in (select if (count(user_id) over(partition by user_id) >= 2, id, 0) from order_info where date >= '202...

Mysql

2021-09-18

2 501