牛客网数据库sql实战剖析(一）

牛客网SQL实战

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

思路：找出入职时间最大的时间，作为子查询条件进一步查找到该员工的其它信息。

SELECT * FROM employees
WHERE hire_date = (
    SELECT MAX(hire_date)
    FROM employees
);

这里不能用order by的方式，因为入职时间最大的可能不唯一。

SELECT * FROM employees
WHERE hire_date order by hier_date limit 1;

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

思路：降序排名，跳过两条取一条（即第三条）

1）方法一：limit 2,1
select * from employees where hire_date=(
    select distinct hire_date 
    from employees 
    order by hire_date desc 
    limit 2,1
)
2）方法二：limit 1 offset 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

思路：使用left（right） join可以减少关联次数(全部字段直接用 tablename.*)，另外排序默认是asc升序。

select s.*,d.dept_no from dept_manager d 
left join salaries s 
on s.emp_no=d.emp_no order by s.emp_no;

4.查找所有已经分配部门的员工的last_name和first_name以及dept_no

思路：left(right) join

select e.last_name,e.first_name,d.dept_no from employees e 
right join dept_emp d
on e.emp_no=d.emp_no;

5.查找所有员工的last_name和first_name以及对应部门编号dept_no

思路：left(right) join

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

6.查找薪水记录超过15条的员工号emp_no以及其对应的记录次数t

思路：按照emp_no分组，然后在分组里面继续用having筛选。

select emp_no,count(emp_no) t from salaries 
group by emp_no 
having t>15;

7.找出所有员工当前薪水salary情况

思路：按照salary分组，然后直接select即可（或者直接用distinct）

select salary 
from salaries 
group by salary 
order by salary desc;
或者
select distinct(salary) 
from salaries 
order by salary desc;

8.获取所有非manager的员工emp_no

思路：利用左连接未匹配到为空来匹配null值,null的即为非mananger。或者使用not in。
普及一下各个连接的关系：

select e.emp_no 
from employees e left join dept_manager d
on e.emp_no=d.emp_no
where d.dept_no is null;
或者
方法1：NOT IN+子查询
select emp_no
from employees
where emp_no not in (select emp_no from dept_manager);

9.获取所有员工当前的manager

思路：用INNER JOIN+不等于

select e.emp_no,m.emp_no manager
from dept_emp e
left join dept_manager m
on e.dept_no=m.dept_no
where e.emp_no 
not in(select emp_no from dept_manager)
或者用INNER JOIN+不等于
SELECT e.emp_no, m.emp_no AS manager_no
FROM dept_emp AS e
INNER JOIN dept_manager AS m
ON e.dept_no=m.dept_no
WHERE e.emp_no!=m.emp_no
AND e.to_date='9999-01-01'
AND m.to_date='9999-01-01'
补充：别名最好取de，dm
补充：不等于可以用<>或者!=表示

10.获取每个部门中当前员工薪水最高的相关信息（#）

思路：一张表记录最高薪水（部门编号,当前最高薪水），一张表记录所有员工的部门及薪水信息（部门编号,员工编号,当前薪水），用部门编号和薪水相等取到最高薪水的员工ID。
注意：1）有多条相同的max salary的情况；2）emp_no是非聚合字段，不能出现在SELECT。因为一个聚合字段(dept_no)对应多个非聚合字段(emp_no)，所以选择的时候，会随机选择非聚合字段中的任何一个，于是出错。

select uni.dept_no, uni.emp_no, max_salary.salary
from
    (select d.dept_no, s.emp_no, s.salary
     from dept_emp d join salaries s
     on d.emp_no = s.emp_no
     and d.to_date = '9999-01-01'
     and s.to_date = '9999-01-01'  
    ) as uni, /* 部门编号,员工编号,当前薪水 */
    (select d.dept_no, max(s.salary) as salary
     from dept_emp d join salaries s
     on d.emp_no = s.emp_no
     and d.to_date = '9999-01-01'
     and s.to_date = '9999-01-01'  
     group by d.dept_no
    ) as max_salary /* 部门编号,当前最高薪水 */
where uni.salary = max_salary.salary
and uni.dept_no = max_salary.dept_no
order by uni.dept_no;

补充：如果这题不需要给出emp_no(即只求所有部门中当前员工薪水最高值)，则用INNER JOIN和GROUP BY和MAX即可解决：
SELECT d.dept_no, MAX(s.salary)
FROM dept_emp as d
INNER JOIN salaries as s
ON d.emp_no=s.emp_no
AND d.to_date=‘9999-01-01’
AND s.to_date=‘9999-01-01’
GROUP BY d.dept_no;