牛客404425958号

分类

/ 注册

全部文章（共214篇）

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

select distinct dept_no, dept_name, title, count(emp_no) over(partition by dept_no,title) as count from dept_emp left join departments us...

2024-10-22

0 90

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

with t1 as( select dept_emp.emp_no, dept_manager.emp_no as manager_no, salary as emp_salary from dept_emp left join salari...

2024-10-22

0 62

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

select dept_no, emp_no, salary from employees left join dept_emp using(emp_no) left join salaries using(emp_no) where emp_no not in (select emp_no fro...

2024-10-22

0 60

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

select emp_no, salary, dense_rank() over(order by salary desc) as t_rank from salaries order by t_rank,emp_no dense_rank() 相同数值排序相同，不跳过下一位最后一行...

2024-10-21

0 65

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

select dept_no, dept_name, count(salary) as sum from departments right join dept_emp using(dept_no) right join salaries using(emp_no) group by dept_no...

2024-10-21

0 61

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

/*在职员工：to_date='9999-01-01' 入职以来的涨薪：先给同一个员工的薪资时期排序（按照from_date升序）；将最早薪资和最近薪资并列，构造临时表，计算差值。 */ with t1 as( select *, row_number() over(par...

2024-10-21

0 60

把标题长度限制那么短干啥！！！！

select last_name, first_name, dept_name from employees left join dept_emp using(emp_no) left join departments using(dept_no)

2024-10-20

0 67

获取薪水第二多的员工的emp_no及其对应的薪水salary

/*自链接，s1.salary<s2.salary 按照s1分组，s2计数有2个时，就是排名第二的薪资。 */ with t as( select s1.salary from salaries s1 join salaries s2 on s1.salary<=s2.salar...

2024-10-20

0 139

获取薪水第二多的员工的empno以及其对应的薪水salary

select emp_no, salary, last_name, first_name from employees left join salaries using(emp_no) where salary=(select max(salary) fro...

2024-10-20

0 58

获取当前薪水第二多的员工的emp_no及其薪水salary

with t as( select emp_no, salary, dense_rank() over(order by salary desc) as salary_rank from salaries ) select emp_no,salary from t where salar...

2024-10-20

0 54