牛牛菌不是男娘

分类

/ 注册

全部文章（共59篇）

题解 | 统计加班员工占比

select department ,concat(round(sum(if(timestampdiff(minute,first_clockin,last_clockin) / 60 > 9.5, 1, 0)) / count(*) * 100, 1),'%') ratio from st...

2025-09-05

0 8

题解 | 统计各个部门平均薪资

select department ,round(avg(normal_salary - dock_salary), 3) avg_salary from staff_tb st inner join salary_tb sa on st.staff_id = sa.staff_id where n...

2025-09-05

0 12

题解 | 查询单日多次下订单的用户信息？

select date(order_time) order_date ,o.user_id user_id ,count(*) order_nums ,vip from order_tb o inner join uservip_tb u on o.user_id = u.user_id group...

2025-09-05

0 8

题解 | 更新用户积分信息？

with t as ( select u.user_id ,sum(if(order_price > 100, order_price, 0)) new_point ,point original_point from uservip_tb u left join order_tb o o...

2025-09-05

0 8

题解 | 统计用户获得积分

select user_id ,sum(floor(timestampdiff(minute,visit_time,leave_time) / 10)) point from visit_tb group by user_id order by point desc

2025-09-05

0 7

题解 | 查询下订单用户访问次数？

select user_id ,count(*) visit_nums from visit_tb where date(visit_time) = '2022-09-02' and user_id in ( select user_id from order_tb ...

2025-09-05

0 7

题解 | 统计各等级会员用户下订单总额

select vip ,if(sum(order_price) is not null, sum(order_price), 0) order_total from order_tb o right join uservip_tb u on o.user_id = u.user_id group b...

2025-09-05

0 7

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

select u.vip vip ,count(*) visit_nums ,count(distinct v.user_id) visit_users from visit_tb v inner join uservip_tb u on v.user_id = u.user_id group by...

2025-09-05

0 8

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

with t as (select user_id ,(day(log_time) - rank()over(partition by user_id order by log_time)) log_initial_rank from login_tb) , t2 as ( select use...

2025-09-05

0 10

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

with sum_quantity as ( select p.product_id ,sum(quantity) quantity from products p right join orders o on p.product_id = o.product_id group by product...

2025-09-04

0 8