会编程的发呆爱好者加麻加辣

分类

/ 注册

全部文章（共208篇）

题解 | 统计商家不同会员每日访问人次及访问人数

select u.vip, count(v.user_id) as visit_nums,count(distinct v.user_id) as visit_users from visit_tb v join uservip_tb u on v.user_id = u.use...

2025-04-04

0 27

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

select s.post,round(avg(TIMESTAMPDIFF(minute, a.first_clockin, a.last_clockin)/60),3) as work_hours from staff_tb s join attendent_tb a on s.staff_id ...

2025-04-03

0 35

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

select product_name,total_sales, row_number() over(partition by category order by total_sales desc, product_id) as category_rank from( select p.p...

2025-04-03

0 29

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

select round(avg(score),3) as avg_score from (select distinct u.user_id,u.score from recommend_tb r join user_action_tb u on r.rec_user = u.user_id wh...

2025-04-03

0 31

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

select c.staff_id, t.staff_name from staff_tb t join cultivate_tb c on t.staff_id = c.staff_id where c.course like '%course3%' 考察like

2025-04-03

0 32

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

select sum(length(course)-length(replace(course,',',''))+1) as staff_nums from cultivate_tb 参考大佬写法：用逗号分割的元素的个数

2025-04-02

0 30

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

select user_id,room_id,room_type,days from( select c.user_id,c.room_id,g.room_type, datediff(c.checkout_time ,c.checkin_time) as days from guestroom_...

2025-04-02

0 33

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

select substr(t.t_time,1,7) as time, sum(t.t_amount) as total from trade t join customer c on t.t_cus = c.c_id where c.c_name = 'Tom' and year(t.t_ti...

2025-04-02

0 37

题解 | 分析客户逾期情况

select c.pay_ability, concat(round(count(case when l.overdue_days is not null then l.customer_id else null end)/count(l.customer_id)*100,1),'%') as ov...

2025-04-02

0 27

题解 | 最长连续登录天数

select user_id,max(cnt) as max_consec_days from (select user_id,primary_date,count(*) as cnt from (select user_id, date_sub(fdate, interval row_number...

2025-04-01

0 33