自连接查询，全连接
数据的列装换成行
去除重复元素
排序

1965. 丢失信息的雇员

表: Employees

+-------------+---------+
| Column Name | Type    |
+-------------+---------+
| employee_id | int     |
| name        | varchar |
+-------------+---------+
employee_id 是这个表的主键。
每一行表示雇员的id 和他的姓名

1
2
3
4
5
6
7
8
9

表: Salaries

+-------------+---------+
| Column Name | Type    |
+-------------+---------+
| employee_id | int     |
| salary      | int     |
+-------------+---------+
employee_id is 这个表的主键。
每一行表示雇员的id 和他的薪水
1
2
3
4
5
6
7
8

写出一个查询语句，找到所有 丢失信息 的雇员id。当满足下面一个条件时，就被认为是雇员的信息丢失：

雇员的 姓名 丢失了，或者
雇员的 薪水信息 丢失了，或者
返回这些雇员的id employee_id ， 从小到大排序 。

查询结果格式如下面的例子所示。

输入：
Employees table:
+-------------+----------+
| employee_id | name     |
+-------------+----------+
| 2           | Crew     |
| 4           | Haven    |
| 5           | Kristian |
+-------------+----------+
Salaries table:
+-------------+--------+
| employee_id | salary |
+-------------+--------+
| 5           | 76071  |
| 1           | 22517  |
| 4           | 63539  |
+-------------+--------+
输出：
+-------------+
| employee_id |
+-------------+
| 1           |
| 2           |
+-------------+
解释：
雇员1，2，4，5 都工作在这个公司。
1号雇员的姓名丢失了。
2号雇员的薪水信息丢失了。

来源：力扣（LeetCode）
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30

题解：

select e.employee_id
from employees e left join salaries s
on e.employee_id=s.employee_id
where salary is null
UNION ALL  
select s.employee_id
from employees e right join salaries s
on e.employee_id=s.employee_id
where e.name is null
order by employee_id;
1
2
3
4
5
6
7
8
9
10

1795. 每个产品在不同商店的价格

表：Products

+-------------+---------+
| Column Name | Type    |
+-------------+---------+
| product_id  | int     |
| store1      | int     |
| store2      | int     |
| store3      | int     |
+-------------+---------+
这张表的主键是product_id（产品Id）。
每行存储了这一产品在不同商店store1, store2, store3的价格。
如果这一产品在商店里没有出售，则值将为null。
1
2
3
4
5
6
7
8
9
10
11

请你重构 Products 表，查询每个产品在不同商店的价格，使得输出的格式变为(product_id, store, price) 。如果这一产品在商店里没有出售，则不输出这一行。

输出结果表中的 顺序不作要求 。

查询输出格式请参考下面示例。

输入：
Products table:
+------------+--------+--------+--------+
| product_id | store1 | store2 | store3 |
+------------+--------+--------+--------+
| 0          | 95     | 100    | 105    |
| 1          | 70     | null   | 80     |
+------------+--------+--------+--------+
输出：
+------------+--------+-------+
| product_id | store  | price |
+------------+--------+-------+
| 0          | store1 | 95    |
| 0          | store2 | 100   |
| 0          | store3 | 105   |
| 1          | store1 | 70    |
| 1          | store3 | 80    |
+------------+--------+-------+
解释：
产品0在store1，store2,store3的价格分别为95,100,105。
产品1在store1，store3的价格分别为70,80。在store2无法买到。

来源：力扣（LeetCode）
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23

题解：

select product_id,'store1' store,store1 price
from Products
where store1 is not null
UNION ALL
select product_id,'store2' store,store2 price
from Products
where store2 is not null
UNION ALL
select product_id,'store3' store,store3 price
from Products
where store3 is not null在这里插入代码片**
1
2
3
4
5
6
7
8
9
10
11

608. 树节点

给定一个表 tree，id 是树节点的编号， p_id 是它父节点的 id 。

+----+------+
| id | p_id |
+----+------+
| 1  | null |
| 2  | 1    |
| 3  | 1    |
| 4  | 2    |
| 5  | 2    |
+----+------+
1
2
3
4
5
6
7
8
9

树中每个节点属于以下三种类型之一：

• 叶子：如果这个节点没有任何孩子节点。
• 根：如果这个节点是整棵树的根，即没有父节点。
• 内部节点：如果这个节点既不是叶子节点也不是根节点。

写一个查询语句，输出所有节点的编号和节点的类型，并将结果按照节点编号排序。上面样例的结果为：

+----+------+
| id | Type |
+----+------+
| 1  | Root |
| 2  | Inner|
| 3  | Leaf |
| 4  | Leaf |
| 5  | Leaf |
+----+------+

1
2
3
4
5
6
7
8
9
10

解释

• 节点 ‘1’ 是根节点，因为它的父节点是 NULL ，同时它有孩子节点 ‘2’ 和 ‘3’ 。
• 节点 ‘2’ 是内部节点，因为它有父节点 ‘1’ ，也有孩子节点 ‘4’ 和 ‘5’ 。
• 节点 ‘3’, ‘4’ 和 ‘5’ 都是叶子节点，因为它们都有父节点同时没有孩子节点。
  样例中树的形态如下：

			  1
			/   \
          2       3
       /   \
    4       5

来源：力扣（LeetCode）
1
2
3
4
5
6
7

题解：

使用自连接的方式解题
通过第一个表的id和第二个表的p_id进行左连接

+----+------+
| id | p_id |
+----+------+
| 1  | null |
| 2  | 1    |
| 3  | 1    |
| 4  | 2    |
| 5  | 2    |
+----+------+
1
2
3
4
5
6
7
8
9

自连接以后的表,左自连接

t1.id  t1.p_id   t2.id,   t2.p_id
1       null       2         1
1       null       2         1
2       1          4         2
2       1          4         2
4       2          null      null
5       2          null      null
1
2
3
4
5
6
7

可以观察到 t1.p_id 为null的节点上是 Root
所有数据均不为null的节点是 Inner
t2.id,t2.p_id 为null的节点为 Leaf

# Write your MySQL query statement below
select DISTINCT t1.id "id",case when t1.p_id is null then  'Root'
                                when t2.p_id is null then  'Leaf'
                                else 'Inner'
                                end 'type'
from tree t1 left join tree t2
on t1.id = t2.p_id
1
2
3
4
5
6
7

176. 第二高的薪水

Employee 表

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

1
2
3
4
5
6
7
8
9

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

查询结果如下例所示。

输入：
Employee 表：
+----+--------+
| id | salary |
+----+--------+
| 1  | 100    |
| 2  | 200    |
| 3  | 300    |
+----+--------+
输出：
+---------------------+
| SecondHighestSalary |
+---------------------+
| 200                 |
+---------------------+

实例二
输入：
Employee 表：
+----+--------+
| id | salary |
+----+--------+
| 1  | 100    |
+----+--------+
输出：
+---------------------+
| SecondHighestSalary |
+---------------------+
| null                |
+---------------------+

来源：力扣（LeetCode）
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32

题解

• 临时表

select
    (select distinct Salary
     from Employee
     order by Salary desc
     limit 1, 1) SecondHighestSalary
from dual;
1
2
3
4
5
6

• ifnull

select
    ifnull((select distinct Salary
            FROM Employee
            order by Salary desc
            limit 1, 1),
    null) as SecondHighestSalary
1
2
3
4
5
6