精致的艾伦在学习

分类

/ 注册

全部文章（共11篇）

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

select user_id from( select user_id,date(date_ass),count(user_id) ct from( select user_id,log_time,date_sub(log_time,interval rk d...

2024-08-28

0 121

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

select post,round(avg(timestampdiff(minute,first_clockin,last_clockin))/60,3) work_hours from staff_tb t1 join attendent_tb t2 on t1.staff_id=t2.staff...

2024-08-26

0 132

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

select name product_name,total_sales,category_rank from( select name,sum(quantity) total_sales,row_number() over(partition by category order by sum(qu...

2024-08-23

0 80

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

select avg(score) avg_score from (select rec_user,sum(case when rec_info_l=hobby_l then 1 else 0 end) acc,avg(score) score from recommend_tb t1 join u...

2024-08-23

1 96

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

select t1.staff_id,staff_name from staff_tb t1 join cultivate_tb t2 on t1.staff_id=t2.staff_id where course like "%course3%" order by t1.sta...

2024-08-23

0 87

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

select sum( case when course in ('course1','course2','course3') then 1 when course in ("course1,course2","course1,course3...

2024-08-20

0 150

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

select pay_ability,concat(round((sum(case when overdue_days is null then 0 else 1 end)/count(t1.customer_id))*100,1),'%') overdue_ratio from loan_tb t...

2024-08-17

0 106

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

#连续应该怎么解决~以用户分组，将日期升序后进行排名标签1、2、3、4，若连续，日期减去对应差值得到的新日期均为相同的（这个想法从B站看来的，很巧妙） select user_id,max(days) max_consec_days from( select user_id,fdate_con,co...

2024-08-16

0 147

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

select month,ranking,song_name,play_pv from ( select month(fdate) month,row_number() over(partition by month(fdate) order by count(t1.song_id) des...

2024-08-15

0 114

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

select user_id,t1.room_id,room_type,datediff(checkout_time,checkin_time) days#日期函数：1.提取：年、月、日、日期、年月～year()、month()、day()、date()、date_format(日期，提取格式)2....

2024-08-14

0 144