小艺找到好工作啦

分类

/ 注册

全部文章（共22篇）

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

with t as(select staff_name,post, -- 计算总分钟数，然后除以 60 得到小时数 TIMESTAMPDIFF(MINUTE, first_clockin, last_clockin) / 60 AS work_hours from staff_tb a join...

2025-11-25

0 16

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

WITH product_sales AS ( -- 第一步：计算每个产品的销量和分类（和简化版内层一致） SELECT p.name AS product_name, p.category, SUM(IFNULL(o.quantity, 0)) AS total_...

2025-11-25

0 24

题解 | 最长连续登录天数【“日期差与行号差”方法】

select user_id, max(连续登录天数) as max_consec_days from (select user_id,初始日期,max(日期排序)-min(日期排序)+1 as 连续登录天数 from (select user_id, f...

2025-11-25

2 28

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

with t as (select month(fdate) as `month`, row_number() over (partition by month(fdate) order by count(b.song_id)desc,a.song_id asc) as ranking, song_...

2025-11-24

0 56

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

select date_format(t_time,'%Y-%m') time,sum(t_amount) total from customer c left join trade t on c.c_id=t.t_cus where year(t_time)=2023 and c_name='To...

2025-11-24

0 20

题解 | 分析客户逾期情况

select b.pay_ability,concat(round(avg(is_laolai)*100,1),'%') as overdue_ratio from (SELECT agreement_id,customer_id,overdue_days, case when overdue_da...

2025-11-24

0 25

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

select a.user_id,a.room_id,b.room_type,a.days from (select room_id,user_id,datediff(checkout_time,checkin_time) as days from checkin_tb where datedif...

2025-11-24

0 20

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

select sum(case when course like '%course1%' then 1 else null end) + sum(case when course like '%course2%' then 1 else null end) + sum(case when cour...

2025-11-24

0 25

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

select a.staff_id,staff_name from staff_tb a right join (select info_id,staff_id from cultivate_tb where find_in_set('course3',course)>0) b on a.st...

2025-11-24

0 19

题解 | 统计复旦用户8月练题情况

select device_id,university,count(question_id) as question_cnt,sum(new_result) as right_question_cnt from (select a.device_id,university,question_id, ...

2025-11-21

0 19