牛客网sql练习（23.2.26）

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

LIMIT 1 OFFSET 2 (或是limit 2,1 )-- 去掉排名倒数第一第二的时间，取倒数第三

SELECT * 
FROM employees
WHERE hire_date = (
    SELECT DISTINCT hire_date 
    FROM employees
    ORDER BY hire_date DESC       -- 倒序
    LIMIT 1 OFFSET 2)              -- 去掉排名倒数第一第二的时间，取倒数第三    
--最后一行也可写作 limit 2，1

197 .查找当前薪水详情以及部门编号dept_no。

SELECT
    s.emp_no,
    s.salary,
    s.from_date,
    s.to_date,
    dm.dept_no
FROM
    salaries AS s
    JOIN dept_manager dm ON s.emp_no = dm.emp_no
ORDER BY
    s.emp_no

198. 请你查找所有已经分配部门的员工的last_name和first_name以及dept_no，未分配的部门的员工不显示。（两种方法）

SELECT
    e.last_name,
    e.first_name,
    de.dept_no
FROM
    dept_emp AS de
    LEFT JOIN employees AS e -- 左外部联结
    ON de.emp_no = e.emp_no;

SELECT
    e.last_name,
    e.first_name,
    de.dept_no
FROM
    dept_emp AS de
    JOIN employees AS e -- 内联结
    ON de.emp_no = e.emp_no
WHERE
    de.dept_no IS NOT NULL -- 筛选已经分配部门的员工

199. 请你查找所有已经分配部门的员工的last_name和first_name以及dept_no，也包括暂时没有分配具体部门的员工。

内联结（join），两边表同时有对应的数据，即任何一边缺失数据就不显示;

左联结（left join），读取左边数据表的全部数据，即便右边表无对应数。即右表d中dept_no即使为NULL，也会读取左表e中的全部emp。

SELECT
    e.last_name,
    e.first_name,
    d.dept_no
FROM
    employees AS e
    LEFT JOIN dept_emp AS d ON e.emp_no = d.emp_no;

205 获取所有员工当前的manager

select
    de.emp_no as emp_no,
    dm.emp_no as manager
from
    dept_emp de
    left join dept_manager dm on de.dept_no = dm.dept_no
where
    de.emp_no != dm.emp_no

206. 获取每个部门中当前员工薪水最高的相关信息。

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'   /*d.to_date和s.to_date不写能运行（影响？）*/
     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;

select t1.dept_no, t2.emp_no, t1.salary     --员工表 d， 薪水表 s
from (select d.dept_no, max(s.salary) AS  salary 
          from dept_emp d 
          join salaries s on d.emp_no=s.emp_no 
          group by d.dept_no) AS t1                 --是最高工资工资表  t1
join (select d.emp_no, d.dept_no, s.salary 
          from  dept_emp d                          --部门和薪水信息表   t2
          join salaries s on d.emp_no=s.emp_no）AS t2  
on t1.dept_no=t2.dept_no and t1.salary=t2.salary
order by  t1.dept_no

209 请你查找employees表所有emp_no为奇数，且last_name不为Mary的员工信息，并按照hire_date逆序排列。

取奇数可以用 取余（%2）=1，偶数可以用取余（%2）=0

select emp_no,birth_date,first_name,last_name,gender,hire_date
from employees
where
    last_name != 'Mary'
    and emp_no % 2 = 1
order by
    hire_date desc

211 请你获取薪水第二多的员工的emp_no以及其对应的薪水salary，若有多个员工的薪水为第二多的薪水，则将对应的员工的emp_no和salary全部输出，并按emp_no升序排序。

SELECT emp_no, salary
FROM salaries
WHERE salary = (SELECT salary
                FROM salaries
                WHERE to_date = '9999-01-01'
                GROUP BY salary
                ORDER BY salary DESC LIMIT 1,1 )