牛客987852806号

分类

题解(45)

/ 注册

全部文章（共163篇）

题解 | #查询培训指定课程的员工信息#

select sb.staff_id, sb.staff_name from staff_tb sb inner join cultivate_tb cb on sb.staff_id=cb.staff_id where course like "%course3%"

2024-08-22

0 132

题解 | #推荐内容准确的用户平均评分#

with tiaojian as ( select distinct ub.user_id, score from recommend_tb tb inner join user_action_tb ub on tb.rec_user=ub.user_id and rec_info_l=hobb...

2024-08-22

0 155

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

select post, avg(timestampdiff(second,first_clockin,last_clockin))/3600 as work_hours from staff_tb sb inner join attendent_tb ab on sb.staff_id=ab....

2024-08-22

0 130

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

with tiaojian as ( select tb.user_id, date(log_time) as pw, row_number()over(partition by tb.user_id order by date(log_time) asc ) as pn from regist...

2024-08-22

0 132

题解 | #统计所有课程参加培训人次#

select sum(t.pn) as staff_nums from( select (LENGTH(course) - LENGTH(REPLACE(course, ',', '')))+1 as pn from cultivate_tb ) as t

2024-08-21

0 134

题解 | #查询连续入住多晚的客户信息？#

这题比较容易，就是难在排序了，需要多注意 with tiaojian as ( select user_id, cb.room_id, room_type, sum(datediff(checkout_time,checkin_time)) as pdiff from checkin_tb cb ...

2024-08-21

0 123

题解 | #获取指定客户每月的消费额#

with tiaojian as ( select date_format(t_time,"%Y-%m") as time, t_cus, sum(t_amount) as total from trade where t_type=1 and year(t_time)=2023...

2024-08-21

0 128

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

select pay_ability, concat( round( ( sum(overdue_days is not null)/count(distinct cb.customer_id))*100,1),"%") as overdue_ratio from loan_...

2024-08-21

0 125

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

with tiaojian as ( select user_id, count(user_id) as cnt from( select fdate, user_id, row_number()over(partition by user_id order by fdate) as pn fro...

2024-08-21

0 116

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

with tiaojian as ( select month(fdate) as month, dense_rank()over(partition by month(fdate) order by count(p.song_id) desc,p.song_id asc) as pn, song...

2024-08-21

0 136