总坐高见

分类

/ 注册

全部文章（共19篇）

题解 | 完成员工考核试卷突出的非领导员工

第一种方法： -- 第一步：找出非领导员工 with emp_info_workers as ( select emp_id,emp_level from emp_info where emp_level < 7), -- 第二步：计算答题时间和分数的平均值 exam_record_avg ...

2025-09-05

0 7

题解 | 被重复观看次数最多的3个视频

方法一： -- 第一步：筛选观看次数大于1的视频 with selected_play as ( select uid,cid,count(*) as cnt from play_record_tb group by uid,cid having count(*) >= 2) /* 第二步...

2025-09-04

0 7

题解 | 请写出计算粉丝ctr的sql语句

方法一： select sum(read_num)/sum(show_num) as fans_ctr from a inner join b on a.author_id = b.author_id inner join c on b.content_id = c.content_id whe...

2025-09-04

0 9

题解 | 每天登陆最早的用户的内容喜好

方法一： with min_time_user as ( select user_id,log_time from login_tb where log_time in ( select min(log_time) as min_time from login_tb group by date(l...

2025-09-02

0 7

题解 | 统计加班员工占比

方法一：ifnull() with base_tb1 as( select s.department,count(*) as total from staff_tb s inner join attendent_tb a on s.staff_id = a.staff_id group by dep...

2025-09-02

0 10

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

方法一：round()函数需要写在外层，直接用round(avg(salary) over(partition by department),3)，运行结果不对，会多出一行none值。 with base_tb as( select sta.department,(normal_salary-doc...

2025-09-01

0 10

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

方法一： with base_tb as ( select user_id,sum(order_price) as point_add from ( select o.user_id,order_price from order_tb o inner join uservip_tb u on o....

2025-08-29

0 10

题解 | 统计用户获得积分

方法一： select user_id,floor(sum(timestampdiff(minute,visit_time,leave_time))/10)*1 as point from visit_tb group by user_id order by point desc

2025-08-29

0 7

题解 | 统计用户从访问到下单的转化率

方法一： with daily_order as ( select date(order_time) as date,count(distinct user_id) as order_total from order_tb group by date(order_time) ), daily_vis...

2025-08-29

0 8

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

方法一：case when select u.vip,sum(case when o.order_price is null then 0 else o.order_price end) as order_total from uservip_tb u left join order_tb o on...

2025-08-29

0 6