社畜张二狗

分类

题解(18)

/ 注册

全部文章（共19篇）

题解 | #工作日各时段叫车量、等待接单时间和调度时间#

select case when date_format(a.event_time,'%H-%i-%s')<'07-00-00' then '休息时间' when date_format(a.event_time,'%H-%i-%s')<...

Mysql

2022-07-05

0 200

题解 | #连续签到领金币#

select uid,date_format(t,'%Y%m') as month, sum(case rn%7 when 3 then 3 when 0 then 7 else 1 end) as coin from(select uid,t,dt,row_number() over(partit...

Mysql

2022-02-11

0 259

题解 | #牛客直播开始时各直播间在线人数#

select a.course_id,a.course_name,count(distinct b.user_id) as online_pv from course_tb a left join attend_tb b on a.course_id=b.course_id where time(b...

Mysql

2022-01-18

0 230

题解 | #某宝店铺连续2天及以上购物的用户及其对应的天数#

select user_id,max(cnt) from(select user_id, date_add(sales_date,interval -rn day) as t, count(*) as cnt from(select distinct user_id...

Mysql

2022-01-18

0 236

题解 | #大小写混乱时的筛选统计#

select c.tag,d.cnt from( select a.tag,count(b.start_time) as cnt from examination_info a,exam_record b where a.exam_id=b.exam_id group by 1) c, ( sel...

Mysql

2022-01-17

0 232

题解 | #各城市最大同时等车人数#

select city,max(uv) as max_wait_uv from(select city,t, sum(rn) over(partition by city,date(t) order by t,rn desc) as uv from(select city,event...

Mysql

2022-01-17

1 333

题解 | #根据指定记录是否存在输出不同情况#

select uid,level,cnt_incom_0,cnt_incom,rate_incom,cnt_t, max(cnt_incom_0) over() as max_cnt_incom_0 from( select distinct a.uid,a.level, count(if(a.le...

Mysql

2022-01-16

0 238

题解 | #筛选昵称规则和试卷规则的作答记录#

select a.uid,b.exam_id,round(avg(b.score),0) as avg_score from user_info a,exam_record b,examination_info c where a.uid=b.uid and b.exam_id=c.exam_id...

Mysql

2022-01-16

0 249

题解 | #每月及截止当月的答题情况#

select distinct m,m_uv,new_m_uv, max(new_m_uv) over(order by m asc) as max_new_m_uv, sum(new_m_uv) over(order by m asc) as cum_m_uv from( select disti...

Mysql

2022-01-16

0 219

题解 | #每份试卷每月作答数和截止当月的作答总数。#

select distinct exam_id,date_format(start_time,'%Y%m') as m, count(id) over(partition by exam_id, date_format(start_time,'%Y%m') ) as cnt_m, count(id...

Mysql

2022-01-16

0 233