旭寒丶

分类

/ 注册

全部文章（共23篇）

题解 | 未完成试卷数大于1的有效用户

select uid,sum(case when submit_time is null then 1 else 0 end) incomplete_cnt,sum(case when submit_time is not null then 1 else 0 end) complete_cnt,g...

2025-05-06

0 37

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

select emp_id,emp_level,exam_tag from (select exam_record.emp_id emp_id,emp_level,tag exam_tag,exam_record.exam_id,timestampdiff(second,start_time,sub...

2025-04-17

0 33

题解 | 短视频直播间晚上11-12点之间各直播间的在线人数

select uv.room_id,room_name,count(distinct uv.user_id) user_count from user_view_tb uv join room_info_tb rt on uv.room_id=rt.room_id where left(in_tim...

2025-04-17

0 31

题解 | 截至当月的练题情况

select device_id,ym,sum(cnt)over(partition by device_id order by ym) sum_cnt, round((cnt+lag(cnt,1,0)over(partition by device_id order by ym)+lag(cnt...

2025-04-12

0 38

题解 | 任意两个连续自然月练题次数大于1的用户

select distinct device_id from (select device_id,year1,month1,lag(month1)over(partition by device_id order by year1,month1) month2,lag(year1)over(part...

2025-04-12

0 40

题解 | 最后一个匹配上的位置

select device_id,char_length(nick_name)-position("cba" in reverse(nick_name))-1 last_idx,mid(nick_name,length(nick_name)-position("cba&...

2025-04-12

0 47

题解 | 用户名规范化

select device_id,concat(upper(left(SUBSTRING_INDEX(blog_url,"/",-1),1)),lower(mid(SUBSTRING_INDEX(blog_url,"/",-1),2,100))) user_n...

2025-04-12

0 26

题解 | 用户昵称超长的用户

select device_id,nick_name,CHAR_LENGTH(nick_name) nick_len from user_submit where CHAR_LENGTH(nick_name)>4 order by device_id desc 两个函数的功能：LENGTH()...

2025-04-12

0 30

题解 | 用户每月最后一周的练题数

select device_id,ym,count(*) cnt from (select device_id,concat(year(event_date),"年",mid(event_date,6,2),"月") ym,(case when day(eve...

2025-04-12

0 37

题解 | 每月各旬有多少人练题

select case when (day(event_date) between 1 and 9) then concat(year(event_date),"年",mid(event_date,6,2),"月上旬") when (day(event_dat...

2025-04-12

0 38