牛客网SQL

2021/3/25 ——2021/4/15 第二轮刷题

SQL1

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

# 方法1：使用子查询

--获取所有最晚的hire_date,然后把与其相等的记录选出来，可以选多条

-- date类型的数据越大，表示时间越接近现在，即最晚，使用MAX()函数

-- where语句使用=和in都可得到结果

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


# 方法2：使用limit或者offset关键字

--通过倒序排序，查询第一条数据，就是最晚入职的信息，但是最晚入职可能不止一人，可能存在同一时间入职多人

SELECT * FROM employees
ORDER BY hire_date DESC LIMIT 1;
#
select * from employees
order by hire_date desc limit 0,1;
#
select * from employees
order by hire_date desc limit 1 offset 0;

# 补充：
-- limit n：表示从第0条数据开始，读取n条数据，是limt(0, n)的缩写
-- limit m,n：表示从第m条数据开始，读取n条数据
-- limit n offset m：表示从第m条数据开始，读取n条数据

SQL2

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

--存在员工入职时间可能一致的问题


# 方法1：子查询(最佳)

select * from employees
where hire_date =(
    select distinct hire_date 
    from employees
    order by hire_date desc
    limit 1 offset 2);
#
select * from employees
where hire_date =(
    select hire_date from employees
    group by hire_date
    order by hire_date desc
    limit 1 offset 2);

# 补充：
# 若存在多人(如3人)同时在最晚的一天入职的情形，必须要考虑去重(使用distinct或者group by)
distinct和group by的执行顺序都在limit前面

# 方法2：使用limit或者offset关键字

select * from employees
order by hire_date desc limit 2,1;
# 
select * from employees
order by hire_date desc limit 1 offset 2;

-- 总结：
-- 方法1适用于存在多个员工入职同时达到最晚一天的情况。泛用性更好。

 

SQL8

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

select distinct salary
from salaries
where to_date = '9999-01-01'
order by salary desc;

--注意：是当前时间

 SQL10

请你找出所有非部门领导的员工emp_no

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

--左连接+is null
--左连接左边所有表都会有，右边没有的自动补为null

SQL11

获取所有的员工和员工对应的经理，如果员工本身是经理的话则不显示

select de.emp_no, dm.emp_no manager
from dept_emp de
join dept_manager dm
on de.dept_no = dm.dept_no
where de.emp_no <> dm.emp_no
and dm.to_date = '9999-01-01'
and de.to_date = '9999-01-01';

--可以先筛选出当前日期的manager和员工
--且员工ID与manager不一样

 SQL12

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

-- 错误：d.emp_no是非聚合字段，不能出现在SELECT。因为一个聚合字段(dept_no)对应多个非聚合字段(emp_no)，所以选择的时候，会随机选择非聚合字段中的任何一个，于是出错。

-- 聚合函数通过作用一组值而只返回一个单一值，因此，在SELECT语句中出现的字段要么为一个聚合函数的输入值，如COUNT(course)，要么为GROUP BY语句中指定的字段，要么是常数，否则会出错。

-- 简而言之：使用GROUP BY子句时，SELECT子句中只能有聚合键、聚合函数、常数

SELECT a.dept_no,b.emp_no,a.salary
FROM
    (SELECT dept_no,MAX(salary) AS salary
    FROM salaries
    INNER JOIN dept_emp
    ON dept_emp.emp_no=salaries.emp_no
    WHERE dept_emp.to_date = '9999-01-01'
    AND salaries.to_date='9999-01-01'
    GROUP BY dept_no) AS a                         --找出各部门最高薪资
INNER JOIN
    (SELECT dept_no,dept_emp.emp_