2021-05-20 牛客sql刷题（1-10）

0520，雪在努力加班，我在努力刷题，今天想明白了一些事，加油吧，小c,留给你的时间不多了。
为了更美好的明天而战。

猴子和未名学院的课看完了，但是sql需要大量的实战练习。所以sqlzoo和牛客sql练习同步入手，牛客上题目讨论比较多，每一道题我会自己去做然后结合别人的代码做简单的思考。

1. 查找最晚入职的员工
考察：这里主要知识点是limit的应用，limit 0,1和limit 1 是一样的，但是可能会有相同时间的问题存在，所以最后一个max是更科学的查询方式：

select * from employees
order by hire_date desc
limit 1;

/* 使用limit 与 offset关键字  */

select * from employees
order by hire_date desc
limit 1 offset 0;

/* 使用limit关键字 从第0条记录 向后读取一个，也就是第一条记录 */

select * from employees
order by hire_date desc
limit 0,1;

/* 使用子查询，最后一天的时间有多个员工信息 */

select * from employees
where hire_date = (select max(hire_date) from employees);

2. 查找employees里入职员工时间排名倒数第三的员工所有信息
考察：这里主要是对limit和offset的用法的考虑

写法1（如果有多人同时入职，可能会产生错误）
        select *
        from employees
        order by hire_date desc
        limit 1 offset 2;

    写法2
        SELECT *
        FROM employees
        WHERE hire_date = (
        SELECT DISTINCT hire_date
        FROM employees
        ORDER BY hire_date DESC       -- 倒序
        LIMIT 1 OFFSET 2);              -- 去掉排名倒数第一第二的时间，取倒数第三

    补充：
    以下的两种方式均表示取2,3,4三条条数据。
    1.select* from test LIMIT 1,3;
    当limit后面跟两个参数的时候，第一个数表示要跳过的数量，后一位表示要取的数量。
    2.select * from test LIMIT 3 OFFSET 1;(在mysql 5以后支持这种写法)
    当 limit和offset组合使用的时候，limit后面只能有一个参数，表示要取的的数量,offset表示要跳过的数量 。

3. 查找当前领导薪水详情及部门编号
考察：联接以及order by 排序

select a.*,b.dept_no
from salaries as a left join dept_manager as b
on a.emp_no = b.emp_no
where a.to_date = '9999-01-01' and b.to_date='9999-01-01'
order by b.emp_no asc;

4. 查找已分配部门员工的last_name,first_name,dept_no，没有分配的被忽略掉
考察：联接以及这个题实际a表可能有空值，但是b表是没有空值的，别人的答案都是在select里选上b.dept_no 在后面就不需要再加非空筛选了

select a.last_name,a.first_name,dept_no
from employees as a left join dept_emp as b 
on a.emp_no = b.emp_no
where b.dept_no is not null;
别人的代码
SELECT e.last_name, e.first_name, d.dept_no
FROM employees AS e, dept_emp AS d
WHERE e.emp_no = d.emp_no;
或者
SELECT e.last_name, e.first_name, d.dept_no
FROM employees AS e INNER JOIN dept_emp AS d
ON e.emp_no=d.emp_no;

5. 查找已分配部门员工的last_name,first_name,dept_no，全都要
考察：联接，直接left join就完事了，还是大表与小表之间谁驱动谁的关系

select a.last_name,a.first_name,b.dept_no
from employees as a left join dept_emp as b
on a.emp_no = b.emp_no;

补充：有句话叫做：小表驱动大表，就是说小的数据集驱动大得数据集，A200条，B200000条，如果A在外层，表连接来说就只连接200次，反之需要200000次，大大浪费了资源。
    1.当使用left join时，左表是驱动表，右表是被驱动表 ;
    2.当使用right join时，右表时驱动表，左表是被驱动表 ;
    3.当使用inner join时，mysql会选择数据量比较小的表作为驱动表，大表作为被驱动表 ;

PPS.在数量级悬殊较大时，也可以用mapjoin

6.查找薪水记录超过15次的员工号emp_no以及其对应的记录次数t
考察：分组及组内查询

   select emp_no,count(emp_no) as t
    from salaries
    group by emp_no
    having t > 15;
    博客上的不同观点：（如果是涨幅，是要去除掉第一项的，且需要去重）
    select amp_no, count(distinct from_date)-1 as t
    from salaries
    group by emp_no
    having t>15
    

7. 找出所有员工具体的薪水salary情况，并逆序排列
   考察：去重及排序

select distinct salary
from salaries
order by salary desc;

8. 找出所有非部门领导的员工emp_no
   考察：不在范围内/或者也可以用联接非去处理

   思路1：not in
        select employees.emp_no
        from employees
        where emp_no not in(select emp_no from dept_manager);

    思路2：left join is null
        select a.emp_no
        from employees as a left join dept_manager as b
        on a.emp_no = b.emp_no
        where dept_no is null;

9. 获取所有员工当前的manager，如果当前的manager是自己的话结果不显示
考察：不等于可以用!=或<>表示；以及联接知识

	SELECT e.emp_no, m.emp_no AS manager_no
    FROM dept_emp AS e
    left 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';

10. 获取每个部门中当前员工薪水最高的相关信息，给出dept_no, emp_no以及其对应的salary，按照部门编号升序排列

考察：前10题里最难的一题，要把两个表都联接起来，并做筛分；高票回答里有用开窗函数，这个今天经哥的课也提过，OLAP？

select r.dept_no,ss.emp_no,r.maxSalary from (
select d.dept_no,max(s.salary)as maxSalary from dept_emp d,salaries s
where 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 r,salaries ss,dept_emp dd
where r.maxSalary=ss.salary
and r.dept_no=dd.dept_no
and dd.emp_no=ss.emp_no
and ss.to_date='9999-01-01'
and dd.to_date='9999-01-01'
order by r.dept_no asc

明天翻翻sql基础教程里的group by以及窗口函数有关内容。