Jecyyy

分类

/ 注册

全部文章（共7篇）

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

- 分窗函数先算出每个部门的薪资排名，然后再嵌套查询排名第一的人 select dept_no, emp_no, salary as maxSalary from (select dept_no, emp_no, salary, rank() over (partition by dept_no o...

2024-09-16

0 73

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

select emp_no, salary, dense_rank() over (order by salary desc) t_rank from salaries order by t_rank, emp_no

2024-09-16

0 84

题解 | #获取当前薪水第二多的员工的emp_no以及其对

- 取最大的薪水并把它排除，之后再去取最大的薪水，即是第二大的薪水 select emp_no,salary,last_name,first_name from employees a left join salaries b using(emp_no) where salary = (selec...

2024-09-16

0 107

题解 | #查找所有员工的last_name和first

select a.last_name, a.first_name, b.dept_no from employees a left join dept_emp b using(emp_no)

2024-09-16

0 76

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

select emp_no, salary, a.from_date, b.to_date, dept_no from salaries a right join dept_manager b using(emp_no) order by a.emp_no

2024-09-16

0 76

题解 | #统计各岗位员工平均工作时长#

select post, round(avg(wh),3) work_hours from ( select post, timestampdiff(minute,first_clockin,last_clockin)/60 as wh from staff_tb a left joi...

2024-09-15

0 114

题解 | #每个商品的销售总额#

select product_name, total_sales, rank() over (partition by cate order by total_sales desc) as category_rank from (select a.name as prod...

2024-09-15

3 123