SQL - SQL Day1 - 《Leetcode 刷题》

#175 组合两个表
#176. 第二高的薪水
#177. 第N高的薪水
- 关于 Rank 的一些知识点
  - 1、rank( ) over( )
  - 2、dense_rank( ) over( )
#178. 分数排名
#180. 连续出现的数字
- Lag() 和 Lead() 函数
  - Lag() 函数：列表向下偏移
  - Lead() 函数：列表向上偏移
今日知识点

#175 组合两个表

https://leetcode-cn.com/problems/combine-two-tables/

+-------------+---------+
| 列名         | 类型     |
+-------------+---------+
| PersonId    | int     |
| FirstName   | varchar |
| LastName    | varchar |
+-------------+---------+
PersonId 是上表主键

+-------------+---------+
| 列名         | 类型    |
+-------------+---------+
| AddressId   | int     |
| PersonId    | int     |
| City        | varchar |
| State       | varchar |
+-------------+---------+
AddressId 是上表主键

编写一个 SQL 查询，满足条件：无论 person 是否有地址信息，都需要基于上述两表提供 person 的以下信息：

FirstName, LastName, City, State

# Write your MySQL query statement below

# 题目中提到无论person是否有地址信息，都要提供表
# 这就说明 Person 是主表，用left join来连接两个表

Select P.FirstName, P.LastName, A.City, A.State
From Person as P LEFT JOIN Address as A
    ON P.PersonId = A.PersonId

#176. 第二高的薪水

https://leetcode-cn.com/problems/second-highest-salary/

+-------------+------+
| Column Name | Type |
+-------------+------+
| id          | int  |
| salary      | int  |
+-------------+------+
id 是这个表的主键。
表的每一行包含员工的工资信息。

编写一个 SQL 查询，获取并返回 Employee 表中第二高的薪水。如果不存在第二高的薪水，查询应该返回 null 。

# Write your MySQL query statement below

# 利用limit选出第二高的薪水
# 注意如果不存在第二高的薪水，要返回null值
# 我们这里使用max嵌套来返回空值为null

SELECT max(a.salary) SecondHighestSalary
FROM 
    (SELECT DISTINCT salary
     FROM employee
     ORDER BY salary DESC
     LIMIT 1,1) a
     # LIMIT后的第一个参数是输出记录的初始位置，
     # 第二个参数偏移量，偏移多少，输出的条目就是多少。
     # 这里我们设置salary最高的为1，偏移1为就是排名第二高的

#177. 第N高的薪水

https://leetcode-cn.com/problems/nth-highest-salary/

+-------------+------+
| Column Name | Type |
+-------------+------+
| id          | int  |
| salary      | int  |
+-------------+------+
Id是该表的主键列。
该表的每一行都包含有关员工工资的信息。

编写一个SQL查询来报告 Employee 表中第 n 高的工资。如果没有第 n 个最高工资，查询应该报告为 null 。
查询结果格式如下所示。

CREATE FUNCTION getNthHighestSalary(N INT) RETURNS INT
BEGIN
  RETURN (
      # Write your MySQL query statement below.
      SELECT DISTINCT a.salary getNthHighestSalary
      FROM
        (
            SELECT salary, DENSE_RANK() OVER (ORDER BY salary DESC) Rank_D
            FROM employee
        ) a
        WHERE a.Rank_D=n
  );
END

关于 Rank 的一些知识点

1、rank( ) over( )

使用 rank()函数，over() 中order by列如果出现重复，下一序号将跳过重复的笔数顺延

select *,rank() over(partition by subject order by sorce desc) from test

2、dense_rank( ) over( )

使用dense_rank() 函数，窗口函数over() 中order by 列如果出现重复，下一序号将顺延

select *,dense_rank() over(partition by subject order by sorce desc) from test

#178. 分数排名

https://leetcode-cn.com/problems/rank-scores/

+-------------+---------+
| Column Name | Type    |
+-------------+---------+
| id          | int     |
| score       | decimal |
+-------------+---------+
Id是该表的主键。
该表的每一行都包含了一场比赛的分数。Score是一个有两位小数点的浮点值。

编写 SQL 查询对分数进行排序。排名按以下规则计算:

分数应按从高到低排列。
如果两个分数相等，那么两个分数的排名应该相同。
在排名相同的分数后，排名数应该是下一个连续的整数。换句话说，排名之间不应该有空缺的数字。
按 score 降序返回结果表。

# Write your MySQL query statement below

SELECT a.score, a.rank
FROM
(
    # 这里要特别注意的是：因为我们已经在前面有了rank，要用``包裹rank来命名
    SELECT score, DENSE_RANK() OVER (ORDER BY score DESC) as `rank`
    FROM Scores
) a

#180. 连续出现的数字

https://leetcode-cn.com/problems/consecutive-numbers/

+-------------+---------+
| Column Name | Type    |
+-------------+---------+
| id          | int     |
| num         | varchar |
+-------------+---------+
id 是这个表的主键。

编写一个 SQL 查询，查找所有至少连续出现三次的数字。
返回的结果表中的数据可以按 任意顺序 排列。

# Write your MySQL query statement below

SELECT DISTINCT a.num ConsecutiveNums
FROM
(
    SELECT num,
    # 指的是让表格数据向下移动一位
    lead(num,1) over (ORDER BY id DESC) n1,
    # 指的是让表格数据向下移动两位位
    lead(num,2) over (ORDER BY id DESC) n2
    FROM Logs
) a
# a中我们将有三列，如果这一列中的num、n1、n2相等，那么我们就保留这个数

WHERE a.num=a.n1 and a.num=a.n2

Lag() 和 Lead() 函数

Lag() 函数：列表向下偏移

SELECT ID,NUM,
 LAG(NUM) OVER (PARTITION BY ID ORDER BY NUM) AS OneArgs,
 LAG(NUM,1) OVER (PARTITION BY ID ORDER BY NUM) AS TowArgs,
 LAG(NUM,2,0) OVER (PARTITION BY ID ORDER BY NUM) AS ThressArgs
 FROM T

LAG 以当前行之前的给定物理偏移量来提供对行的访问。在 SELECT 语句中使用此分析函数可将当前行中的值与先前行中的值进行比较。
偏移量中默认为null，可以修改为任意数字，上述例子中设置为了0
SQL Day1 - 图3

Lead() 函数：列表向上偏移

LEAD函数与LAG函数刚刚相反，它是向前偏移指定的行数，默认是1行。

SELECT ID,NUM,
 LEAD(NUM) OVER (PARTITION BY ID ORDER BY NUM) AS OneArgs,
 LEAD(NUM,1) OVER (PARTITION BY ID ORDER BY NUM) AS TowArgs,
 LEAD(NUM,2,0) OVER (PARTITION BY ID ORDER BY NUM) AS ThressArgs
 FROM T

SQL Day1 - 图4

今日知识点

表的连接
limit函数能够q取偏移量
窗口函数lead() 和lag() 的用法
dense_rank() over ()的用法
遇到需要返回null时，可以考虑使用max、min来嵌套