🥸 团 数分笔试
有一个员工表dept_emp有如下字段:
- 员工ID:emp_no
- 部门ID:dept_no
- 入职时间:from_date
- 离职时间:to_date
有员工工资表salaries,包含如下字段:
- 员工ID:emp_no
- 入职时间:from_date
- 离职时间:to_date
- 薪水:salary
- 获取所有部门中员工薪水最高的相关信息,给出dept_no, emp_no以及其对应的salary,按照部门编号升序排列
- 查找所有员工自入职以来的薪水涨幅情况,给出员工编号emp_no以及其对应的薪水涨幅growth,并按照growth进行升序
-- 获取所有部门中员工薪水最高的相关信息,给出dept_no, emp_no以及其对应的salary,按照部门编号升序排列
select a.dept_no,a.emp_no,b.salary as top_salary
from
(
select a.dept_no,
a.empt_no,b.salary,
dense_rank() over(partition by a.dept_no order by b.salary desc) as rank
from
(
select a.emp_no,
a.dept_no,
b.salary
from dept_emp as a
join salaries as b
on a.emp_no=b.emp_no
where b.to_date > now()
)
) a
where a.rank=1
order by a.dept_no asc
dense_rank()函数需要在子查询的select语句中使用,而不是在子查询的from语句中使用,所以要变成一个临时表
-- chatgpt 我永远的老师 5555 好亚撒西 😎👌🕶😭
-- 查找所有员工自入职以来的薪水涨幅情况,给出员工编号emp_no以及其对应的薪水涨幅growth,并按照growth进行升序
select emp_no,max_s-min_s as growth
from(
select emp_no,max(salary) as max_s,min(salary) as min_s
from salaries
where to_date > now() and from_date <= now()
)
group by emp_no
order by growth asc
问题:有员工工作时长表dataList,包含如下字段:员工ID:emp_no;月份:date;工时:dur_time
-- 计算每个员工1-3月每月工时及总工时
select emp_no,month(date), month_dur,sum(month_dur) over(partition by emp_no order by month(date)) as sum_time
from (
select date,month(date),sum(dur_time) as month_dur
from datalist
where month(date)>=1 and month(date)<=3
group by emp_no,month(date)
)
group by emp_no,month(date)