壹壹风荷举

分类

/ 注册

全部文章（共47篇）

题解 | 更新员工信息表

SELECT ei.EMPLOYEE_ID, -- 如果 eu.EMPLOYEE_ID 是 NULL (表示该员工无更新记录)，则直接用主表职位。 -- 否则，比较日期，决定使用新职位还是旧职位。 CASE WHEN eu.EMPLOYEE_ID IS...

2025-09-21

0 14

题解 | 输出播放量最高的视频

-- 1. 将播放记录的起止时间(start/end)拆解为+1和-1的离散事件流。 WITH events AS ( SELECT cid, start_time as event_time, 1 as event_type FROM play_record_tb UNIO...

2025-09-20

0 12

题解 | 输出播放量最高的视频

WITH time_points AS ( -- 收集所有可能的关键时间点 SELECT DISTINCT cid, start_time as check_time FROM play_record_tb UNION SELECT DISTINCT cid...

2025-09-20

0 13

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

with temp1 as ( select avg(score) over ( partition by exam_id ) as avg_score, ...

2025-09-20

0 13

题解 | 网易云音乐推荐(网易校招笔试真题)

with temp as ( select follower_id from follow where user_id=1 ), temp1 as ( SELECT DISTINCT music_id from music_likes ml join temp f o...

2025-09-18

0 23

题解 | 统计加班员工占比

with temp as ( select department,sum(case when timestampdiff(second,first_clockin,last_clockin)/3600>9.5 then 1 else 0 end) as ot_count ,count(*)...

2025-09-15

0 19

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

with temp as (select user_id,sum( order_price) as add_point from order_tb where order_price>100 group by user_id) select t.user_id,add_point+point...

2025-09-15

0 18

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

with temp as (select date(visit_time) as date_visit,count(distinct user_id) as visit_count from visit_tb group by date(visit_time) ), temp2 as ( ...

2025-09-15

0 19

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

SELECT user_id, COUNT(*) as visit_nums FROM visit_tb WHERE user_id IN ( SELECT DISTINCT user_id FROM order_tb WHERE DATE(order_time) = '20...

2025-09-15

0 11

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

with temp as ( select user_id,log_date, case when datediff(log_date,lag(log_date,1,log_date) over(partition by user_id order by log_date)) >...

2025-09-14

1 17