已run的羚羊很纯真

分类

/ 注册

全部文章（共20篇）

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

select `date`, round(avg(if(type='no_completed',1,0)),3) p from( select send_id, receive_id, type, `date` from email e left join `user` u1 on e.send_i...

2024-11-01

1 60

题解 | #获取有奖金的员工相关信息。#

select s.emp_no, first_name, last_name, btype, salary, round((case when btype = 1 then salary*0.1 when btype = 2 then salary*0.2 else salary*0.3 end),...

2024-10-30

1 52

题解 force index(索引列名)

select * from salaries force index(idx_emp_no) where emp_no = '10005'

2024-10-28

1 72

题解 | #汇总各个部门当前员工的title类型的分配数目#

select dp.dept_no, dept_name, title, count(*) as count from dept_emp dp join titles t on dp.emp_no = t.emp_no left join departments d on dp.dept_no = ...

2024-10-27

1 73

题解 | #获取所有非manager员工当前的薪水情况#

select de.dept_no, e.emp_no, salary from employees e left join salaries s on e.emp_no = s.emp_no left join dept_emp de on e.emp_no = de.emp_no inner j...

2024-10-27

1 68

题解:对所有员工的薪水按照salary降序进行1-N的排名

select emp_no, salary, dense_rank()over(order by salary desc) t_rank from salaries where to_date = '9999-01-01'

2024-10-27

1 40

题解 | #统计各个部门的工资记录数#

select de.dept_no, dept_name, count(*) sum from salaries s left join dept_emp de on s.emp_no = de.emp_no left join departments d on de.dept_no = d.dep...

2024-10-27

1 40

题解 | #查找在职员工自入职以来的薪水涨幅情况#

select a.emp_no, `salary_end`-`salary_start` growth from (select e.emp_no, salary as salary_start from employees e left join salaries s on e.emp_no = ...

2024-10-27

1 51

题解 | #获取每个部门中当前员工薪水最高的相关信息#

# 使用窗口函数求出每个分区（dept_no）按salary降序的排序，再取出每个区排名第一的信息。 select dept_no, emp_no, salary maxSalary from (select dept_no, d.emp_no, salary, rank()over(partiti...

2024-10-25

1 56

题解 | #每个月Top3的周杰伦歌曲#

select * from (select month(p.fdate) `month`, row_number()over(partition by month(fdate) order by count(p.user_id) desc, p.song_id asc) ranking, song_...

2024-10-21

3 59