牛客SQL练习笔记

题目一：获取当前（to_date='9999-01-01'）薪水第二多的员工的emp_no以及其对应的薪水salary

获取当前（to_date='9999-01-01'）薪水第二多的员工的emp_no以及其对应的薪水salary
CREATE TABLE `salaries` (
`emp_no` int(11) NOT NULL,
`salary` int(11) NOT NULL,
`from_date` date NOT NULL,
`to_date` date NOT NULL,
PRIMARY KEY (`emp_no`,`from_date`));

解题思路：

1.用子查询获取 “薪水第二多的员工”

2.用order by salary desc 来获取逆序的薪资排序结果

3. 用limit 1,1 来获取第二个员工的信息

知识点补充：

limit语法： limit i,j

i为数据条的索引位置
j为从索引位置i开始获取多少条数据

答案：

select emp_no , salary from salaries
where to_date='9999-01-01' 
and salary = (
    select distinct salary from salaries order by salary desc  limit 1,1  
)

题目二：查找当前薪水(to_date='9999-01-01')排名第二多的员工编号emp_no、薪水salary、last_name以及first_name，不准使用order by

CREATE TABLE `employees` (
`emp_no` int(11) NOT NULL,
`birth_date` date NOT NULL,
`first_name` varchar(14) NOT NULL,
`last_name` varchar(16) NOT NULL,
`gender` char(1) NOT NULL,
`hire_date` date NOT NULL,
PRIMARY KEY (`emp_no`));


CREATE TABLE `salaries` (
`emp_no` int(11) NOT NULL,
`salary` int(11) NOT NULL,
`from_date` date NOT NULL,
`to_date` date NOT NULL,
PRIMARY KEY (`emp_no`,`from_date`));

解题思路：

两表连接
排除最大数据的数据集中的最大数据就是原始数据集中的第二大数据：排除了最高薪资后的其他数据中的最高薪资，就是第二高的薪资。

知识点补充：

聚合函数的出现与Where和Having无关
group by 之后如果需要添加条件用having
聚合函数与Having，Where的关系：where > 聚合函数 > having

聚合函数要比having先执行
where要比聚合函数先执行
group by 要与 having一起出现

答案：

select e.emp_no,Max(s.salary) as salary ,e.last_name,e.first_name 
from employees e inner join salaries s  
on e.emp_no = s.emp_no
where 
s.to_date='9999-01-01' 
and 
s.salary not in (
    select Max(salary) from salaries where to_date='9999-01-01'
)

题目三：查找所有员工的last_name和first_name以及对应的dept_name，也包括暂时没有分配部门的员工(三表)

CREATE TABLE `departments` (
`dept_no` char(4) NOT NULL,
`dept_name` varchar(40) NOT NULL,
PRIMARY KEY (`dept_no`));

CREATE TABLE `dept_emp` (
`emp_no` int(11) NOT NULL,
`dept_no` char(4) NOT NULL,
`from_date` date NOT NULL,
`to_date` date NOT NULL,
PRIMARY KEY (`emp_no`,`dept_no`));

CREATE TABLE `employees` (
`emp_no` int(11) NOT NULL,
`birth_date` date NOT NULL,
`first_name` varchar(14) NOT NULL,
`last_name` varchar(16) NOT NULL,
`gender` char(1) NOT NULL,
`hire_date` date NOT NULL,
PRIMARY KEY (`emp_no`));

解题思路：

通过两次left join 将三表连接，此时需要注意连接顺序

最终目标是要获取部门名称dept_name=null的数据
所以departments应该最后被连接
先通过employees的emp_no字段，连接employees和dept_emp，此时可以获取dept_no
再通过dept_no 将上一次的连接结果连接departments，最后可获取空的dept_name

答案：

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

疑惑：

表结构中每一个字段都不为空，最后结果何来的null值