@Author：Runsen

@Date：2020/9/18

大四刷題拼offer系列，不拼不行啊

現(xiàn)在集中火力進(jìn)軍SQL，然后過渡到Java。下面的SQL題目都是來自牛客網(wǎng)，都是本人覺得挺重要的題目，然后記錄下。

文章目錄

• 查找最晚入職員工的所有信息
• 查找入職員工時(shí)間排名倒數(shù)第三的員工所有信息
• 兩表查詢
• Leetcode175. 組合兩個(gè)表
• 查找所有員工的last_name和first_name以及對(duì)應(yīng)部門編號(hào)dept_no
• Leetcode176 第二高的薪水
• Leetcode 177. 第N高的薪水

查找最晚入職員工的所有信息

這個(gè)題目的是入門的水平，就是目前所有的數(shù)據(jù)里員工入職的日期都不是同一天（sqlite里面的注釋為–，在mysql中為comment）

CREATE TABLE `employees` ( `emp_no` int(11) NOT NULL, -- '員工編號(hào)' `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`));

其實(shí)就是有一個(gè)員工表，查找最晚入職員工的所有信息。

很簡(jiǎn)單直接降序：select * from employees order by hire_date desc limit 1

還有一種方法激素使用子查詢，針對(duì)的是最后一天的時(shí)間有多個(gè)員工信息：select * from employees where hire_date = (select max(hire_date) from employees);

查找入職員工時(shí)間排名倒數(shù)第三的員工所有信息

這個(gè)題目和上面變了，現(xiàn)在尋找倒數(shù)第三。那么就使用limit+offset 。offset 就是區(qū)間長(zhǎng)度的意思，可以是一個(gè)數(shù)，也可以是一個(gè)區(qū)間，記得是從0開始，和Python列表完全一樣。

下面舉幾個(gè)limit+offset 的示例。

以下的兩種方式均表示取2,3,4三條條數(shù)據(jù)。 1.select* from test LIMIT 1,3; 當(dāng)limit后面跟兩個(gè)參數(shù)的時(shí)候，第一個(gè)數(shù)表示要跳過的數(shù)量，后一位表示要取的數(shù)量。2.select * from test LIMIT 3 OFFSET 1;(在mysql 5以后支持這種寫法) 當(dāng) limit和offset組合使用的時(shí)候，limit后面只能有一個(gè)參數(shù)，表示要取的的數(shù)量,offset表示要跳過的數(shù)量 。

查找入職員工時(shí)間排名倒數(shù)第三的員工所有信息的SQl代碼：select * from employees order by hire_date desc limit 1 offset 2

兩表查詢

題目描述：查找各個(gè)部門當(dāng)前(dept_manager.to_date='9999-01-01')領(lǐng)導(dǎo),當(dāng)前(salaries.to_date='9999-01-01')薪水詳情以及其對(duì)應(yīng)部門編號(hào)dept_no
(注:請(qǐng)以salaries表為主表進(jìn)行查詢，輸出結(jié)果以salaries.emp_no升序排序，并且請(qǐng)注意輸出結(jié)果里面dept_no列是最后一列)

CREATE TABLE `salaries` ( `emp_no` int(11) NOT NULL, -- '員工編號(hào)', `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, -- '部門編號(hào)' `emp_no` int(11) NOT NULL, -- '員工編號(hào)' `to_date` date NOT NULL, PRIMARY KEY (`emp_no`,`dept_no`));

最后結(jié)果如下。

這里有兩張表，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. 組合兩個(gè)表

表1: Person +-------------+---------+ | 列名 | 類型 | +-------------+---------+ | PersonId | int | | FirstName | varchar | | LastName | varchar | +-------------+---------+ PersonId 是上表主鍵表2: Address+-------------+---------+ | 列名 | 類型 | +-------------+---------+ | AddressId | int | | PersonId | int | | City | varchar | | State | varchar | +-------------+---------+ AddressId 是上表主鍵 編寫一個(gè) SQL 查詢，滿足條件：無論 person 是否有地址信息，都需要基于上述兩表提供 person 的以下信息：FirstName, LastName, City, State

在Leetcode這題有一個(gè)要求：無論 person 是否有地址信息，都需要基于上述兩表提供 person 的以下信息。其實(shí)這就是左連接。

select FirstName,LastName,City,State from Person left join Address on Person.PersonId = Address.PersonId

查找所有員工的last_name和first_name以及對(duì)應(yīng)部門編號(hào)dept_no

查找所有員工的last_name和first_name以及對(duì)應(yīng)部門編號(hào)dept_no，也包括暫時(shí)沒有分配具體部門的員工(請(qǐng)注意輸出描述里各個(gè)列的前后順序)

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

這個(gè)題和Leetcode中的175. 組合兩個(gè)表完全一樣，就是一個(gè)左連接的問題。
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 第二高的薪水

編寫一個(gè) SQL 查詢，獲取 Employee 表中第二高的薪水（Salary） 。 +----+--------+ | Id | Salary | +----+--------+ | 1 | 100 | | 2 | 200 | | 3 | 300 | +----+--------+ 例如上述 Employee 表，SQL查詢應(yīng)該返回 200 作為第二高的薪水。如果不存在第二高的薪水，那么查詢應(yīng)返回 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)

• 當(dāng)expression獲得數(shù)據(jù)為空的時(shí)候，返回value，有點(diǎn)類似python的 dict.get(x，value)的形式，即當(dāng)一個(gè)查詢沒有對(duì)應(yīng)的值的時(shí)候，返回一個(gè)默認(rèn)值，這個(gè)默認(rèn)值可以自定義

limit x offset y

• 跳過y條記錄，返回x條記錄

order by xx

• 這個(gè)就是按照xx字段排序，后面可以用desc/asc指定是降序還是升序

• 那么，綜合以上，就是要按照Salary字段排序且按降序排序，并跳過排序結(jié)果的第一條，再返回一條

• 因?yàn)槭翘^了降序排序結(jié)果的第一條，再返回一條，那么返回的就是第二高的記錄

• 并用ifnull函數(shù)控制查詢結(jié)果為空的時(shí)候的返回結(jié)果

select ifnull((select distinct Salary from Employee order by Salary desc limit 1 offset 1),null) as SecondHighestSalary

Leetcode 177. 第N高的薪水

編寫一個(gè) SQL 查詢，獲取 Employee 表中第 n 高的薪水（Salary）。+----+--------+ | Id | Salary | +----+--------+ | 1 | 100 | | 2 | 200 | | 3 | 300 | +----+--------+ 例如上述 Employee 表，n = 2 時(shí)，應(yīng)返回第二高的薪水 200。如果不存在第 n 高的薪水，那么查詢應(yīng)返回 null。+------------------------+ | getNthHighestSalary(2) | +------------------------+ | 200 | +------------------------+

代碼邏輯基本和上一題的一樣，就是多了一個(gè)判斷的條件。

CREATE FUNCTION getNthHighestSalary(N INT) RETURNS INT BEGINif 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

今晚先學(xué)到這里了，有點(diǎn)累了。

如果你想跟博主建立親密關(guān)系，可以關(guān)注博主，或者關(guān)注博主公眾號(hào)“Python之王”，了解一個(gè)非本科程序員是如何成長(zhǎng)的。
博主ID：潤(rùn)森（weixin_44510615），希望大家點(diǎn)贊、評(píng)論、收藏

總結(jié)

以上是生活随笔為你收集整理的为了OFFER而战，大四那些日子在牛客网和Leetcode刷SQL题目的全部?jī)?nèi)容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網(wǎng)站內(nèi)容還不錯(cuò)，歡迎將生活随笔推薦給好友。