Bubble2016

分类

题解(21)

/ 注册

全部文章（共17篇）

题解 | #查找所有已经分配部门的员工的last_name和first_name以及dept_no#

select e.last_name,e.first_name,d.dept_no from employees as e left join dept_emp as d on e.emp_no = d.emp_no where d.dept_no is not null;

Sqlite Mysql

2021-09-23

1 343

题解 | #查找当前薪水详情以及部门编号dept_no#

select s.emp_no,s.salary,s.from_date,s.to_date,d.dept_no from salaries as s left join dept_manager as d on s.emp_no = d.emp_no where d.dept_no is not ...

Sqlite Mysql

2021-09-23

1 369

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

with re as (select *, sum(case type when 'add' then grade_num else grade_num*(-1) end) as grade from grade_info group by user_id ) select u.i...

Mysql

2021-09-23

0 283

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

with re as( select *, sum(g.grade_num) as grade_sum from user as u join grade_info as g on u.id = g.user_id group by id order by grade_sum desc...

Mysql

2021-09-23

0 325

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

select a.name,a.grade_sum from (select *, sum(grade_num) over(partition by g.user_id) as grade_sum from grade_info as g join user as u on g.us...

Mysql

2021-09-23

0 337

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

中位数所在区间为最大正序数与最大逆序数均>=二分之一总序数的区间. select grade from ( select * , (select sum(number) as sum from class_grade) as sum, sum(number) ove...

Mysql

2021-09-23

1 428

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

窗口函数，不用分组，直接number求和 select grade, sum(number) over(order by grade) as t_rank from class_grade order by grade;

Mysql

2021-09-22

0 394

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

分别提取2025年和2026年的数据，再join起来，用到截取字符串left(s,n)和right(s,n) select a.job, a.first_year_mon, a.first_year_cnt, b.second_year_mon, ...

Mysql

2021-09-22

0 380

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

group by两个字段 select job,left(date,7) as mon,sum(num) as cnt from resume_info where date like '2025%' group by job,mon order by mon desc,cnt desc;

Mysql

2021-09-22

0 334

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

模糊查找 like 通配符 select job,sum(num) as cnt from resume_info where date like '2025%' group by job order by cnt desc;

Mysql

2021-09-22

3 442