Leetcode数据库

1. 组合两个表

1.1 题目

表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

1.2 答案

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

2. 第二高的薪水

2.1 题目

编写一个 SQL 查询，获取 Employee 表中第二高的薪水（Salary） 。

±—±-------+
| Id | Salary |
±—±-------+
| 1 | 100 |
| 2 | 200 |
| 3 | 300 |
±—±-------+
例如上述 Employee 表，SQL查询应该返回 200 作为第二高的薪水。如果不存在第二高的薪水，那么查询应返回 null。

±--------------------+
| SecondHighestSalary |
±--------------------+
| 200 |
±--------------------+

2.2 知识点

2.2.1 ROW_NUMBER()

row_number()主要是为选出的每一条记录按照一定的排序方式生成一个行序号。

语法：

ROW_NUMBER ( ) OVER ( [ PARTITION BY value_expression , ... [ n ] ] order_by_clause 

例子：

SC表：

--按score列降序排列，并生成行序号
select ROW_NUMBER() over (order by score desc) RowNumber, *
from SC

结果：

2.2.2 with as

with as 也叫做子查询部分，可以定义一个SQL段落，该SQL段落可以被整个SQL语句所用到类似于临时表的作用。with as 可以提高你的SQL语句的可读性，也有可以用在在UNION ALL的不同部分，作为提供临时数据的部分。

CTE完整的描述是公用表表达式（Common Table Expression)是SQL SERVER 2005版本之后引入的一个新特性。CTE可以看作是一个临时的查询结果集，可以在接下来的一个SELECT,INSERT,UPDATE,DELETE,MERGESQL语句中被多次引用。使用公用表达式可以让SQL语句更加可读。

下面是CTE的语法：

[ WITH <common_table_expression> [ ,n ] ] 
<common_table_expression>::= 
        expression_name [ ( column_name [ ,n ] ) ] 
    AS 
        ( CTE_query_definition )

例子：

with
    cte
    as
    (
        select ROW_NUMBER() over (order by score) RowNumber, *
        from SC
    )
select *
from cte

结果：

使用CTE时应注意以下几点：

1. CTE后面必须直接跟使用CTE的SQL语句（如select、insert、update等），否则，CTE将失效。如下面的SQL语句将无法正常使用CTE：

   with
       cte
       as
       (
           select ROW_NUMBER() over (order by score) RowNumber, *
           from SC
       )
   select *
   from SC
   --应将这条SQL语句去掉
   --使用使用CTE的SQL语句应紧跟在相关的CTE后面 
   select *
   from cte
   

2. CTE后面也可以跟其他的CTE，但只能使用一个with，多个CTE中间用逗号（,）分隔，如下面的SQL语句所示：

with
    cte1
    as
    (
        select S#
        from SC
        where S# = '01'
    ),
    cte2
    as
    (
        select *
        from SC
        where score > 80
    )
select *
from cte1 a
    left join cte2 b
    on a.S# = b.S#

详情参考：数据库：SQLServer中with as 用法笔记

2.2.3 MAX()

SQL Server MAX()函数 它返回集合中的最大值。

以下是MAX()函数的语法：

MAX(expression)  

MAX()函数接受一个可以是列或有效表达式的表达式。

与函数类似，MAX()函数忽略NULL值并计算中的所有值。

例子：

select max(score)
from SC

2.2.4 dense_rank()

DENSE_RANK()是一个Windows函数，它为结果集的分区中的每一行分配一个排名。 与RANK()函数不同，DENSE_RANK()函数返回连续的排名值。 如果每个分区中的行具有相同的值，则它们将获得相同的排名。

例子：

select *, DENSE_RANK() OVER(order by score desc) as den_rank
from SC

结果：

2.2.5 SQL Server排名函数（ROW_NUMBER、RANK、DENSE_RANK、NTILE ）区别

网页链接：https://www.cnblogs.com/zhaoshujie/p/9594692.html

2.3 答案

select max(Salary) SecondHighestSalary
from(
select Salary, dense_rank()over(order by Salary DESC) r
    from Employee 
) t
where t.r=2

3. 第N高的薪水

3.1 题目

编写一个 SQL 查询，获取 Employee 表中第 n 高的薪水（Salary）。

±—±-------+
| Id | Salary |
±—±-------+
| 1 | 100 |
| 2 | 200 |
| 3 | 300 |
±—±-------+
例如上述 Employee 表，n = 2 时，应返回第二高的薪水 200。如果不存在第 n 高的薪水，那么查询应返回 null。

±-----------------------+
| getNthHighestSalary(2) |
±-----------------------+
| 200 |
±-----------------------+

3.2答案

CREATE FUNCTION getNthHighestSalary(@N INT) RETURNS INT AS
BEGIN
    RETURN (
        /* Write your T-SQL query statement below. */
        select max(Salary)
    from
        (select dense_rank() over(order by Salary desc) as orderNumber, *
        from Employee) as NthSalary
    where orderNumber = @N
    );
END