muchenfeng

分类

/ 注册

全部文章（共40篇）

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

select emp_no ,salary,last_name,first_name from employees join salaries using(emp_no) where salary in ( select max(salary) from salaries where...

2024-11-08

0 39

获取当前薪水第二多的员工emp_no与其对应的薪水

select emp_no,salary from( select emp_no,salary,dense_rank()over(order by salary desc) rn from salaries )t where rn =2 order by emp_no

2024-11-08

0 40

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

select dept_no,emp_no,Salary salary from( select dept_no,emp_no,Salary,row_number()over(partition by dept_no order by Salary desc) as rn from( select ...

2024-11-08

1 48

题解 | #获取所有员工当前的manager#

select t1.emp_no ,t2.emp_no as manager from( select e.emp_no,d.dept_no from dept_emp e join dept_manager d using(dept_no) )t1 left join ( se...

2024-11-08

0 45

题解 | #最长连续登录天数#

-- 真实的情况下，应该考虑到一个人会出现同一天多次登录的情况 with order_date as( select distinct user_id, date_format(fdate,'%Y-%m-%d') fdate, row_number() over(part...

2024-11-07

0 57

题解 | #查找最晚入职员工的所有信息#

select * from employees order by hire_date desc limit 1

2024-03-12

0 176

题解 | #每个创作者每月的涨粉率及截止当前的总粉丝量#

SELECT author, date_format(start_time,'%Y-%m') month, round(sum(if(if_follow=2,-1,if_follow)) / count(*),3) as fans_growth_rate, ...

2024-02-21

0 185

题解 | #平均播放进度大于60%的视频类别#

SELECT tag, CONCAT(avg_play_progress, "%") as avg_play_progress FROM ( SELECT tag, ROUND(AVG( IF(TIMESTAMPDIFF(SECON...

2024-02-20

0 143

题解 | #各个视频的平均完播率#

SELECT tl.video_id,ROUND(SUM(CASE WHEN (end_time -start_time) >= duration THEN 1 ELSE 0 ...

2024-02-20

0 155

题解 | #查找学校是北大的学生信息#

题解：select device_id ,university from user_profile where univ...

Mysql

2022-06-17

0 342