2021-05-11

力扣SQL语句练习题

第一题

表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


解法一：
select FirstName, LastName, City, State
from person left join address
on person.personId=address.personId;
解法二：
select p.FirstName ,p.lastname,a.city,a.state 
from person as p left join address as a 
on p.personid = a.personid；

1. 题目说：无论 person 是否有地址信息，都需要基于上述两表提供 person 的以下信息： FirstName, LastName, City, State 也就是说，地址信息（City, State）的查询结果是Null是OK的。但是，姓名（FirstName, LastName）必须有。
2. 为啥不用Where？ 因为where的实质就是根据你给的条件（personID相等），选取两表的公共部分。但是，因为PERSON表不是所有人都有地址信息的，但是ADDRESS表只显示有地址信息的人，这样选取出来的就是有地址信息的人，漏掉了没有地址信息的人。where的本质就是过滤。
3. 如何连接？应该用PERSON表左连接（left join） ADDRESS表，保留person表的所有信息
4. 数据库在通过连接两张或多张表来返回记录时，都会生成一张中间的临时表，然后再将这张临时表返回给用户。
5. 在使用left jion时，on和where条件的区别如下：
   on条件是在生成临时表时使用的条件，它不管on中的条件是否为真，都会返回左边表中的记录。
   where条件是在临时表生成好后，再对临时表进行过滤的条件。这时已经没有left join的含义（必须返回左边表的记录）了，条件不为真的就全部过滤掉

两张表的连接

A inner join B       取交集
A left join B        取A的全部，B没有的对应值对应为Null
A right join B       取B的全部，A没有的对应值为Null
A full outer join B  取并集，彼此没有对应的值为 null。 对应条件在 on 后面填写。

第二题

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

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

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

解答：
SELECT
    (SELECT DISTINCT  # distinct去重
            Salary
        FROM
            Employee
        ORDER BY Salary DESC  
        LIMIT 1 OFFSET 1) AS SecondHighestSalary #OFFSET 1代表跳过一个实例，LIMIT 1 代表读取一行数据 
;
# ORDER BY 语句用于根据指定的列对结果集进行排序。
# ORDER BY 语句默认按照升序对记录进行排序。
# 如果想要按照降序对记录进行排序，可以使用 DESC 关键字。

解法二：
select ifnull((select distinct Salary from Employee
order by Salary desc
limit 1,1),null) as SecondHighestSalary;  #limit 0,1, 从你的表中的第0个数据开始，只读取一个；

• IFNULL() 函数用于判断第一个表达式是否为 NULL，如果为 NULL 则返回第二个参数的值，如果不为 NULL 则返回第一个参数的值。
• ORDER BY语句可以对指定的列或者结果集进行排序，默认升序，可以使用DESC关键字变为降序
• as关键字用于起别名
• DISTINCT 关键字与 SELECT 语句一起使用，来消除所有重复的记录，并只获取唯一一次记录
• LIMIT 子句用于限制由 SELECT 语句返回的数据数量。例如

假设 COMPANY 表有以下记录：

ID          NAME        AGE         ADDRESS     SALARY
----------  ----------  ----------  ----------  ----------
1           Paul        32          California  20000.0
2           Allen       25          Texas       15000.0
3           Teddy       23          Norway      20000.0
4           Mark        25          Rich-Mond   65000.0
5           David       27          Texas       85000.0
6           Kim         22          South-Hall  45000.0
7           James       24          Houston     10000.0
下面是一个实例，它限制了您想要从表中提取的行数：

sqlite> SELECT * FROM COMPANY LIMIT 6;
这将产生以下结果：

ID          NAME        AGE         ADDRESS     SALARY
----------  ----------  ----------  ----------  ----------
1           Paul        32          California  20000.0
2           Allen       25          Texas       15000.0
3           Teddy       23          Norway      20000.0
4           Mark        25          Rich-Mond   65000.0
5           David       27          Texas       85000.0
6           Kim         22          South-Hall  45000.0
但是，在某些情况下，可能需要从一个特定的偏移开始提取记录。下面是一个实例，从第三位开始提取 3 个记录：

sqlite> SELECT * FROM COMPANY LIMIT 3 OFFSET 2;
这将产生以下结果：

ID          NAME        AGE         ADDRESS     SALARY
----------  ----------  ----------  ----------  ----------
3           Teddy       23          Norway      20000.0
4           Mark        25          Rich-Mond   65000.0
5           David       27          Texas       85000.0

链接：https://leetcode-cn.com/problems/second-highest-salary/solution/di-er-gao-de-xin-shui-by-leetcode/
题目来源：力扣（LeetCode）