《刷牛客SQL题》Part1

来自牛客SQL题库

有些个人理解的话可能不正确，毕竟纯小白上路，车不稳，有错误恳请大家批评指正。

题目1：

https://www.nowcoder.com/practice/218ae58dfdcd4af195fff264e062138f?tpId=82&tqId=29753&tPage=1&rp=&ru=%2Fta%2Fsql&qru=%2Fta%2Fsql%2Fquestion-ranking
题目描述 employees
查找最晚入职员工的所有信息

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`));

题解：一共5种写法（甚至更多），倒数的2种是最佳写法（此时同一天入职的看做是一样晚的）

/*按时间降序排列，并取第一个。 */
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);

/* 写法5：根据第2题的思路改写*/
SELECT *
FROM employees
WHERE hire_date = (
    SELECT DISTINCT hire_date
    FROM employees
    ORDER BY hire_date DESC       
); 

题目2

https://www.nowcoder.com/practice/ec1ca44c62c14ceb990c3c40def1ec6c?tpId=82&tqId=29754&tPage=1&rp=&ru=%2Fta%2Fsql&qru=%2Fta%2Fsql%2Fquestion-ranking
题目描述
查找入职员工时间排名倒数第三的员工所有信息

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`));

题解：第1和3种解法最佳，因为使用的去重（一天有多个员工入职，将这些同一天的员工视为并列第一）

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

/* 写法2：*/
SELECT * FROM employees
ORDER BY hire_date DESC
LIMIT 1 offset 2;

/* 写法3：*/
select * from employees 
where hire_date = (
    select distinct hire_date 
    from employees 
    order by hire_date desc 
    limit 2,1          --从第2个记录往后读1个，就是第3个了。
)

第3题：

https://www.nowcoder.com/practice/c63c5b54d86e4c6d880e4834bfd70c3b?tpId=82&tqId=29755&tPage=1&rp=&ru=%2Fta%2Fsql&qru=%2Fta%2Fsql%2Fquestion-ranking
题目描述
查找各个部门当前(to_date='9999-01-01')领导当前薪水详情以及其对应部门编号dept_no

//这里建了两个表
CREATE TABLE `dept_manager` (
`dept_no` char(4) NOT NULL,
`emp_no` int(11) NOT NULL,
`from_date` date NOT NULL,
`to_date` date NOT NULL,
PRIMARY KEY (`emp_no`,`dept_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`));

题解：第一个表中用部门编号dept_no，第二个表中用薪水salary。然后以salary表为主表，添加一列dept_no。所以select s.*,d.dept_no选取这么两个东西。emp_no是主键，连接后，以主键相同为标志。

/* 写法1：*/
select s.*,d.dept_no
from salaries as s  
inner join dept_manager as d
on s.emp_no=d.emp_no
where s.to_date='9999-01-01' and d.to_date='9999-01-01'

/* 写法2：*/
SELECT s.*, d.dept_no 
FROM salaries AS s ,  dept_manager AS d
WHERE s.to_date='9999-01-01'
AND d.to_date='9999-01-01'
AND s.emp_no=d.emp_no;

关于为什么一定要两个表格的时间都限制成规定时间（9999-01-01）呢？

• 因为薪水表是按年发的，而题目要查找的是当前的薪水，所以要过滤掉以前（薪水表）。而dept_manager是因为有领导会离职，to_date时间不一定是9999-01-01，所以要过滤过离职的领导（经理表）。

题目4：

https://www.nowcoder.com/practice/6d35b1cd593545ab985a68cd86f28671?tpId=82&tqId=29756&tPage=1&rp=&ru=/ta/sql&qru=/ta/sql/question-ranking
题目描述
查找所有已经分配部门的员工的last_name和first_name以及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`));

题解：

/* 写法1：输出是按employees表输出的，所以使用内连接查询时，必须将employees表放在前面。*/
select last_name,first_name,dept_no 
from employees,dept_emp 
where dept_emp.emp_no = employees.emp_no;    --主键相等

/* 写法2：左联接*/
select e.last_name,e.first_name,d.dept_no 
from dept_emp d left join employees e 
on d.emp_no = e.emp_no 
可以看到e中的员工是一个集合，而分配了部门的员工是这个集合中的子集，
这样部门匹配到的肯定都是分配到该部门员工的信息，直接使用左连接就ok

/* 写法3：内联接*/
select a.last_name, a.first_name, b.dept_no
from employees a inner join dept_emp b
on a.emp_no = b.emp_no

写法2需要动脑子，建议学会写法3。写法3的on也可以用where,但是建议用on。
  对于JOIN参与的表的关联操作，如果需要不满足连接条件的行也在我们的查询范围内的话，我们就必需把连接条件放在ON后面，而不能放在WHERE后面，如果我们把连接条件放在了WHERE后面，那幺所有的LEFT,RIGHT,等这些操作将不起任何作用，对于这种情况，它的效果就完全等同于INNER连接。对于那些不影响选择行的条件，放在ON或者WHERE后面就可以。

题目5：

https://www.nowcoder.com/practice/dbfafafb2ee2482aa390645abd4463bf?tpId=82&tqId=29757&tPage=1&rp=&ru=/ta/sql&qru=/ta/sql/question-ranking
题目描述
查找所有员工的last_name和first_name以及对应部门编号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`));

题解：

/* 写法1：*/
SELECT ep.last_name, ep.first_name, dp.dept_no 
FROM employees ep 
LEFT JOIN dept_emp dp
ON ep.emp_no = dp.emp_no

注意：
INNER JOIN 两边表同时有对应的数据，即任何一边缺失数据就不显示。
LEFT JOIN 会读取左边数据表的全部数据，即便右边表无对应数据。
RIGHT JOIN 会读取右边数据表的全部数据，即便左边表无对应数据。
  依题意，需要大部分employee表的数据，即使dept_emp中没有，所以是左联接。注意，此时的on换成where就不行了。

题目6：

https://www.nowcoder.com/practice/23142e7a23e4480781a3b978b5e0f33a?tpId=82&tqId=29758&tPage=1&rp=&ru=/ta/sql&qru=/ta/sql/question-ranking
题目描述
查找所有员工入职时候的薪水情况，给出emp_no以及salary， 并按照emp_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`));

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`));

题解：2个列，从2个表中选的，

/* 写法1：利用 INNER JOIN 连接两张表*/
SELECT e.emp_no, s.salary 
FROM employees AS e 
INNER JOIN salaries AS s
ON e.emp_no = s.emp_no 
AND e.hire_date = s.from_date
ORDER BY e.emp_no DESC

/* 写法2：直接用逗号并列查询两张表*/
SELECT e.emp_no, s.salary 
FROM employees AS e, salaries AS s
WHERE e.emp_no = s.emp_no 
AND e.hire_date = s.from_date
ORDER BY e.emp_no DESC

/* 写法3：获取salaries中from_data中最小的对应的salary和emp_no，
因为salaries可能涨薪，所以这张表一个员工可能有多条记录。*/
SELECT emp_no,salary 
FROM salaries 
GROUP BY emp_no 
HAVING min( from_date ) 
ORDER BY emp_no DESC

总结：以写法1和2为主，写法3不那么直观。