牛客404425958号

分类

/ 注册

全部文章（共214篇）

题解 | #查找入职员工时间排名倒数第三的员工所有信息#

with t as( select emp_no, birth_date, first_name, last_name, gender, hire_date, dense_rank() over(order by hire_date desc...

2024-10-19

0 65

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

select * from employees where hire_date=(select max(hire_date) from employees)

2024-10-19

0 44

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

/*1.创建一列：每个登录日期的上次登录日期 --2.求每个登录日期和上次登录日期的间隔天数 --3.间隔天数大于1 或空值，标记为1；间隔天数等于1，标记为0 --4.按照用户分组、日期升序，将标记累计求和，以此得到间隔登录日期的分组/次数 --5.求每个用户、每次连续登录的天数 --6.求每个...

2024-10-19

0 50

题解 | #每个月Top3的周杰伦歌曲#

with t as( select month(fdate) as 'month', row_number() over(partition by month(fdate),singer_name order by count(song_name) desc,a.song_id) as rankin...

2024-10-19

0 47

题解 | #每个月Top3的周杰伦歌曲#

with t as( select month(fdate) as 'month', row_number() over(partition by month(fdate) order by count(*) desc,song_id) as ranking, ...

2024-10-19

0 50

题解 | #分析客户逾期情况#

with pay_ability_overdue as( select pay_ability,overdue_days from loan_tb a left join customer_tb b on a.customer_id=b.customer_id) select pay_abilit...

2024-10-19

0 46

题解 | #查询连续登陆的用户#

with t1 as( select user_id, date(log_time) as log_time, rank() over(partition by user_id order by log_time) as rk from l...

2024-10-19

0 36

题解 | #查询连续登陆的用户#

select user_id from( select user_id, date_sub(log_time,interval rk day) as newdate from(select user_id, date(log_time) ...

2024-10-19

0 44

题解 | #统计各岗位员工平均工作时长#

select post, avg(timestampdiff(minute,first_clockin,last_clockin)/60) as work_hours from staff_tb a right join attendent_tb b on a.staff_id=b.s...

2024-10-19

0 46

题解 | #每个商品的销售总额#

select product_name, sum(quantity) as total_sales, rank() over(partition by category order by sum(quantity)desc) as category_rank from( ...

2024-10-18

0 33