SQL笔试题：某团数分岗笔试真题详解

题目（1）
有一个员工表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进行升序
难度：★★★☆☆

<1> 获取所有部门中员工薪水最高的相关信息，给出dept_no, emp_no以及其对应的salary，按照部门编号升序排列

考点：1. 分组排序；2. 表连结；3. 排序

第一步：分组排序，为每个部门员工的薪水进行排序

dense_rank() over(partition by dept_no order by salary desc) rank
第二步：表连结，为薪水找用户ID

a left join b on(a.emp_no=b.emp_no and a.from_date=b.from_date and a.to_date=b.to_date)
第三步：求出排名第一的薪水，并按部门排序

求出排名第一的薪水：where rank=1
按部门排序：order by dept_no desc
组装构成答案

select dept_no
	,emp_no
	,salary
from (
		select a.dept_no as dept_no
		    ,a.emp_no as emp_no
		    ,b.salary as salary
		    ,DENSE_RANK() over (partition by a.dept_no order by b.salary) as rank
		from 
		dept_emp a
		right join 
		(
		    select emp_no
		        ,from_date
		        ,to_date
		        ,salary
		        
		    from salaries
		)b
		on (a.emp_no=b.emp_no and a.from_date=b.from_date and a.to_date=b.to_date)
)
where rank=1
order by dept_no desc

<2> 请你查找所有员工自入职以来的薪水涨幅情况，给出员工编号emp_no以及其对应的薪水涨幅growth，并按照growth进行升序

（注:可能有employees表和salaries表里存在记录的员工，有对应的员工编号和涨薪记录，但是已经离职了，离职的员工salaries表的最新的to_date!=‘9999-01-01’，这样的数据不显示在查找结果里面，以上emp_no为2的就是这样的）

考点：1. 求有条件下的最大最小；2. 表连结；3. 排序

第一步：求有条件下的最大最小，求出员工入职工资及现在工资（需限制目前仍在职）

min(salary) | salary where to_date =‘9999-01-01’
第二步：表连结，为薪水找用户ID

b left join c on (b.emp_no=c.emp_no)
第三步：求出工资涨幅，并涨幅排序

求出工资涨幅：c.salary - b.salary as growth
按涨幅排序：orderby c.salary - b.salary asc
组装构成答案

select c.emp_no as emp_no
    ,c.salary - b.salary as growth
from
(
    select emp_no
        ,min(salary) as salary
        ,from_date
        ,to_date
    from salaries
    group by emp_no
) b
right join
(
    select emp_no
        ,salary
        ,from_date
        ,to_date
    from salaries
    where to_date = '9999-01-01'
) c on (b.emp_no=c.emp_no)
order by c.salary - b.salary asc

题目（2）
有员工工作时长表dataList，包含如下字段：

员工ID：emp_no
月份：date
工时：dur_time
计算每个员工1-3月每月工时及总工时
难度：★★★☆☆

<1> 计算每个员工1-3月每月工时及总工时

考点：1. 按月算员工总工时；2. 算出每个用户的总工时；3. 表连结

第一步：按月算员工总工时

group by emp_no, month(date)
第二步：算出每个用户的总工时

sum() over(partition by emp_no order by date) as sum_time
第三步：表连结

a left join b on(a.emp_no = b.emp_no)
组装构成答案

select emp_no
    ,yue
    ,mon_time
    ,sum_time
from
(
    select emp_no
        ,month(date) yue
        ,sum(dur_time) as mon_time
    from dataList
    where month(date) >=1 and month(date) <=3
    group by emp_no
        ,month(date)
) a
left join 
(
    select emp_no
        ,sum() over (partition by emp_no order by date) as sum_time
    from data2List
) b on (a.emp_no=b.emp_no)