JanisZhan

题解

/ 注册

全部文章 / 题解（共33篇）

题解 | #平均活跃天数和月活人数#

select date_format(submit_time,'%Y%m') as month, round((count(distinct uid,date_format(submit_time,'%y%m%d')))/count(distinct uid),2) as avg_activ...

Mysql

2022-07-07

1 284

题解 | #统计作答次数#

select count(*) as total_pv,count(submit_time) as complete_pv, count(distinct exam_id and submit_time is not null and score is not ...

Mysql

2022-07-06

0 252

题解 | #SQL类别高难度试卷得分的截断平均值#

思路：将表中各科目的最大值和最小值选出组合，然后筛选出分数值不是最值表中的可以确保所有的最大值和最小值排除，避免出现多人同为未全部排除的情况 select ei.tag,ei.difficulty,round(avg(er.score),1) as clip_avg_score from exam...

Mysql

2022-07-06

6 404

题解 | #某乎问答回答过教育类问题的用户里有多少用户回答过职场类问题#

select count(distinct author_id) as nun from answer_tb a left outer join issue_tb i on i.issue_id=a.issue_id where i.issue_type='Career' and ...

Mysql

2022-07-01

0 291

题解 | #某乎问答单日回答问题数大于等于3个的所有用户#

要按时间（先）和作者ID排序升序 select answer_date,author_id,count(author_id) as answer_cnt from answer_tb where month(answer_date)=11 group by author_id,answer_da...

2022-07-01

1 324

题解 | #某乎问答11月份日人均回答量#

select answer_date,round(count(issue_id)/count(distinct author_id),2) as per_num from answer_tb group by answer_date order by answer_date asc

Mysql

2022-07-01

1 350

题解 | #牛客直播转换率#

select b.course_id,c.course_name,round(sum(b.if_sign)*100/sum(b.if_vw),2) as 'sign_rate(%)' from course_tb c right outer join behavior_tb b on ...

Mysql

2022-07-01

1 292

题解 | #某宝店铺折扣率#

tag_price只是单价要记得乘数量 select round(sum(s.sales_price)*100/sum(s.sales_num*p.tag_price),2) as 'discount_rate(%)' from sales_tb s left outer join product_...

Mysql

2022-07-01

5 459

题解 | #获取员工其当前的薪水比其manager当前薪水还高的相关信息#

思路是先分别建立员工工资表和经理工资表，再连表查询 select e.emp_no,m.manager_no,e.emp_salary,m.manager_salary from ( select dm.dept_no,dm.emp_no as manager_no,s.salary...

Mysql

2022-06-30

1 259

题解 | #最差是第几名(一)#

利用 over（ order by xxx）计算每等级及以上的数量 select grade,sum(number) over(order by grade) as t_rank from class_grade

Mysql

2022-06-30

0 242