MySQL/Hive

一、牛客SQL题

简单

1. 查找最晚入职员工的所有信息

#入职最晚的不止一个人
select *
from employees
where hire_date = (
    select max(hire_date)
    from employees
)

2. 入职员工时间排名倒数第三的员工所有信息

select *
from employees
where hire_date = (
    select distinct hire_date
    from employees
    order by hire_date desc
    limit 1 offset 2  #
    #从第二个开始，选一个，也就是第三个
);

3. 各个部门领导薪水详情以及其对应部门编号dept_no

select s.*,d.dept_no
from salaries s
join dept_manager d
on s.emp_no = d.emp_no
and s.to_date = '9999-01-01'
and d.to_date = '9999-01-01'

4. 查找所有已经分配部门的员工

select e.last_name,e.first_name,d.dept_no
from employees e
join dept_emp d
on e.emp_no=d.emp_no
#未分配部门的员工不显示
where d.dept_no is not null;

5. 查找薪水记录超过15次的员工

select s.emp_no,count(s.salary) c
from salaries s
group by s.emp_no
having c>15

6. 所有非部门领导的员工emp_no

select emp_no
from employees
where emp_no not in (
    select emp_no
    from dept_manager
)

7. 获取所有的员工和员工对应的经理

如果员工本身是经理的话则不显示

select e.emp_no,d.emp_no manager
from dept_emp e,dept_manager d
where e.dept_no = d.dept_no
and e.emp_no <> d.emp_no

8. emp_no为奇数，且last_name不为Mary

select *
from employees e
where e.emp_no%2 = 1  （或者e.emp_no&1=1 )
and e.last_name!='Mary'
order by e.hire_date desc

9. 各个title类型的平均工资avg

select t.title,avg(s.salary) as average
from titles as t
join salaries as s
on t.emp_no = s.emp_no
group by t.title
order by average

10. 三表连接

select e.last_name,e.first_name,d.dept_name
from employees e 
left join dept_emp de on e.emp_no=de.emp_no
left join departments d on de.dept_no=d.dept_no

11.更新操作：薪水增加10%

update salaries as s join emp_bonus as e on s.emp_no=e.emp_no
set salary=salary*1.1
where to_date='9999-01-01'

中等

1. 获取薪水第二多的员工（多个，不用order by）

-- 方法一
select s.emp_no, s.salary, e.last_name, e.first_name
from salaries s join employees e
on s.emp_no = e.emp_no
where s.salary =              -- 第三步: 将第二高工资作为查询条件
    (
    select max(salary)        -- 第二步: 查出除了原表最高工资以外的最高工资(第二高工资)
    from salaries
    where salary <    
        (
        select max(salary)    -- 第一步: 查出原表最高工资
        from salaries
        where to_date = '9999-01-01'   
        )
    and to_date = '9999-01-01'
    )
and s.to_date = '9999-01-01'

2. 入职以来的薪水涨幅情况

select b.emp_no,(b.salary-a.salary) as growth
from 
	(select e.emp_no,s.salary
	from employees e 
	left join salaries  s 
	on e.emp_no=s.emp_no
	and e.hire_date=s.from_date)a -- 入职工资表
inner join 
	(select e.emp_no,s.salary
	from employees e 
	left join s