为了OFFER而战，大四那些日子在牛客网和Leetcode刷SQL题目
@Author：Runsen
@Date：2020/9/18
大四刷题拼offer系列，不拼不行啊
现在集中火力进军SQL，然后过渡到Java。下面的SQL题目都是来自牛客网，都是本人觉得挺重要的题目，然后记录下。
查找最晚入职员工的所有信息
这个题目的是入门的水平，就是目前所有的数据里员工入职的日期都不是同一天（sqlite里面的注释为–，在mysql中为comment）
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`));
其实就是有一个员工表，查找最晚入职员工的所有信息。
很简单直接降序：select * from employees order by hire_date desc limit 1
还有一种方法激素使用子查询，针对的是最后一天的时间有多个员工信息：select * from employees where hire_date = (select max(hire_date) from employees);
查找入职员工时间排名倒数第三的员工所有信息
这个题目和上面变了，现在寻找倒数第三。那么就使用limit+offset 。offset 就是区间长度的意思，可以是一个数，也可以是一个区间，记得是从0开始，和Python列表完全一样。
下面举几个limit+offset 的示例。
以下的两种方式均表示取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表示要跳过的数量 。
查找入职员工时间排名倒数第三的员工所有信息的SQl代码：select * from employees order by hire_date desc limit 1 offset 2
两表查询
题目描述：查找各个部门当前(dept_manager.to_date='9999-01-01')领导,当前(salaries.to_date='9999-01-01')薪水详情以及其对应部门编号dept_no
(注:请以salaries表为主表进行查询，输出结果以salaries.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`));
CREATE TABLE `dept_manager` (
`dept_no` char(4) NOT NULL, -- '部门编号'
`emp_no` int(11) NOT NULL, -- '员工编号'
`to_date` date NOT NULL,
PRIMARY KEY (`emp_no`,`dept_no`));
最后结果如下。
这里有两张表，dept_manager和salaries的to_date都要等于9999-01-01，而且dept_manager和salaries的emp_no要相等，具体代码如下。
select salaries.emp_no, salary, salaries.from_date, salaries.to_date, dept_no
from salaries, dept_manager
where dept_manager.to_date='9999-01-01' and salaries.to_date='9999-01-01'
and salaries.emp_no=dept_manager.emp_no
order by salaries.emp_no asc;
select s.*, d.dept_no
from salaries s inner join dept_manager d on s.emp_no=d.emp_no
where s.to_date='9999-01-01'
and d.to_date='9999-01-01'
order by s.emp_no
Leetcode175. 组合两个表
表1: Person
+-------------+---------+
| 列名 | 类型 |
+-------------+---------+
| PersonId | int |
| FirstName | varchar |
| LastName | varchar |
+-------------+---------+
PersonId 是上表主键
表2: Address
+-------------+---------+
| 列名 | 类型 |
+-------------+---------+
| AddressId | int |
| PersonId | int |
| City | varchar |
| State | varchar |
+-------------+---------+
AddressId 是上表主键
编写一个 SQL 查询，满足条件：无论 person 是否有地址信息，都需要基于上述两表提供 person 的以下信息：
FirstName, LastName, City, State
在Leetcode这题有一个要求：无论 person 是否有地址信息，都需要基于上述两表提供 person 的以下信息。其实这就是左连接。
select FirstName,LastName,City,State from Person left join Address on Person.PersonId = Address.PersonId
查找所有员工的last_name和first_name以及对应部门编号dept_no
查找所有员工的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`));
这个题和Leetcode中的175. 组合两个表完全一样，就是一个左连接的问题。
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;
Leetcode176 第二高的薪水
编写一个 SQL 查询，获取 Employee 表中第二高的薪水（Salary） 。
+----+--------+
| Id | Salary |
+----+--------+
| 1 | 100 |
| 2 | 200 |
| 3 | 300 |
+----+--------+
例如上述 Employee 表，SQL查询应该返回 200 作为第二高的薪水。如果不存在第二高的薪水，那么查询应返回 null。
+---------------------+
| SecondHighestSalary |
+---------------------+
| 200 |
+---------------------+
首先考虑选取最高工资的， 然后再选取次高， 其中用到了嵌套
SELECT max(Salary) AS SecondHighestSalary FROM Employee WHERE Salary < (SELECT MAX(Salary) FROM Employee);
用order BY 排序，再LIMIT output，输出更加快。
SELECT max(Salary) AS SecondHighestSalary FROM Employee WHERE Salary < (SELECT Salary
FROM Employee ORDER BY Salary DESC LIMIT 1);
最好的方法就是使用ifnull + limit + offset
ifnull(expression,value)
- 当expression获得数据为空的时候，返回value，有点类似python的 dict.get(x，value)的形式，即当一个查询没有对应的值的时候，返回一个默认值，这个默认值可以自定义
limit x offset y
- 跳过y条记录，返回x条记录
order by xx
-
这个就是按照xx字段排序，后面可以用desc/asc指定是降序还是升序
-
那么，综合以上，就是要按照Salary字段排序且按降序排序，并跳过排序结果的第一条，再返回一条
-
因为是跳过了降序排序结果的第一条，再返回一条，那么返回的就是第二高的记录
-
并用ifnull函数控制查询结果为空的时候的返回结果
select ifnull((select distinct Salary from Employee order by Salary desc limit 1 offset 1),null) as SecondHighestSalary
Leetcode 177. 第N高的薪水
编写一个 SQL 查询，获取 Employee 表中第 n 高的薪水（Salary）。
+----+--------+
| Id | Salary |
+----+--------+
| 1 | 100 |
| 2 | 200 |
| 3 | 300 |
+----+--------+
例如上述 Employee 表，n = 2 时，应返回第二高的薪水 200。如果不存在第 n 高的薪水，那么查询应返回 null。
+------------------------+
| getNthHighestSalary(2) |
+------------------------+
| 200 |
+------------------------+
代码逻辑基本和上一题的一样，就是多了一个判断的条件。
CREATE FUNCTION getNthHighestSalary(N INT) RETURNS INT
BEGIN
if N<0 then RETURN (select min(Salary) from Employee);
else set N = N-1; RETURN ( # Write your MySQL query statement below. select ifnull((select distinct Salary from Employee order by Salary desc limit N,1),null) as NthHighestSalay );
end if;
END
今晚先学到这里了，有点累了。
如果你想跟博主建立亲密关系，可以关注博主，或者关注博主公众号“Python之王”，了解一个非本科程序员是如何成长的。
博主ID：润森（weixin_44510615），希望大家点赞、评论、收藏
文章来源: maoli.blog.csdn.net，作者：刘润森！，版权归原作者所有，如需转载，请联系作者。
原文链接：maoli.blog.csdn.net/article/details/108673115
- 点赞
- 收藏
- 关注作者
评论（0）