JanisZhan

分类

题解(47)

/ 注册

全部文章（共33篇）

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

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

Mysql

2022-06-30

0 242

题解 | #牛客的课程订单分析(三)#

select * from order_info where date > '2025-10-15' and status = 'completed' and product_name in ('C++', 'Java', 'Python') and user_id in (select us...

Mysql

2022-06-29

0 228

题解 | #牛客的课程订单分析(二)#

要记得筛选 status 为 completed 的表示订单成交成功的 select user_id from ( select user_id,count(1) as total from order_info where date>'2025-10-15' ...

Mysql

2022-06-29

1 270

题解 | #考试分数(二)#

建立一个表得出每个科目的平均分，再进行连表比较 select id,job,score from grade left join (select job as j,avg(score) as av from grade group by job) a on grade.job=a.j whe...

Mysql

2022-06-29

1 218

题解 | #异常的邮件概率#

直接先筛选出不是黑名单的用户（发送和接受者） select e.date, round(sum(case when e.type='no_completed' then 1 else 0 end)/count(*),3) as p from email e where e.send_id not...

Mysql

2022-06-28

1 285

题解 | #实习广场投递简历分析(二)#

选取月份补0方法： right(100+month(选取的月份）,2) select job, concat(year(date),'-',right(100+month(date),2)) as mon, sum(num) as cnt from resume_info where year(da...

Mysql

2022-06-28

0 240

题解 | #实习广场投递简历分析(一)#

year(字段名）可直接获取年份 select job, sum(num) as cnt from resume_info where year(date)=2025 group by job order by cnt desc

Mysql

2022-06-28

1 241

题解 | #获得积分最多的人(一)#

用Limite 查询出最大值想问下大家为什么group by 要加name select u.name,sum(g.grade_num) as total from user u left outer join grade_info g on u.id=g.use...

Mysql

2022-06-28

1 206

题解 | #今天的刷题量(一)#

在mysql 中可用 current_date表示当日的日期 select sj.name,count(*) as cnt from submission sb left outer join subject sj on sb.subject_id=sj.id where sb.crea...

Mysql

2022-06-28

1 213

题解 | #平均工资#

查询出salaries 中除去了最大值和最小值后的表再进行计算可以确保最大值和最小值不止一条的情况去除所有最大值最小值 select avg(salary) as avg_salary from salaries where to_date='9999-01-01' and salary!=(s...

Mysql

2022-06-28

1 281