【Leecode-专项突破-SQL入门】day4

周末了也不能太懈怠，要抽点时间学习哦。
这次的专项训练为组合查询 & 指定选取，共 4 道题：2 简单 2 中等

1965.丢失信息的雇员

Employees table:
+-------------+----------+
| employee_id | name     |
+-------------+----------+
| 2           | Crew     |
| 4           | Haven    |
| 5           | Kristian |
+-------------+----------+
Salaries table:
+-------------+--------+
| employee_id | salary |
+-------------+--------+
| 5           | 76071  |
| 1           | 22517  |
| 4           | 63539  |
+-------------+--------+

写出一个查询语句，找到所有丢失信息的雇员id。当满足下面一个条件时，就被认为是雇员的信息丢失：
（1）雇员的姓名丢失
（2）雇员的薪水信息丢失
返回这些雇员的employee_id ， 从小到大排序。
当我看到这道题，我脑海里马上就有一个思路，就是对这两张表做关联查询，因为按照题意，只要有一个字段关联后为 null 即可判断为丢失信息的雇员。

select nvl(e.employee_id,s.employee_id)
  from Employees e FULL OUTER JOIN Salaries s 
  on e.employee_id = s.employee_id
  where e.name is null or s.salary is null
  order by employee_id

采用hiveSQL的函数 nvl() 在MySQL中好像运行不了，但是这主要提供的是一个解题的思路。
那么如果按照官方的解法，需要分别做左（右）关联，然后通过 union 获取最后的并集，这种方式更易理解。

#left join and union
select employee_id from(
select e.employee_id 
  from Employees e left join Salaries s on e.employee_id = s.employee_id
  where s.salary is null
union
select s.employee_id 
  from Employees e right join Salaries s on e.employee_id = s.employee_id
  where e.name is null
) t order by employee_id

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

#输入：
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    |
+------------+--------+-------+

这题为典型的行转列
（1）按照自己的思路（复杂）

select product_id, store, price from(
select product_id
  ,case when store1 is not null then 'store1' else null end as 'store'
  ,case when store1 is not null then store1 else null end as 'price'
  from Products
union all
select product_id
  ,case when store2 is not null then 'store2' else null end as 'store'
  ,case when store2 is not null then store2 else null end as 'price'
  from Products
union all
select product_id
  ,case when store3 is not null then 'store3' else null end as 'store'
  ,case when store3 is not null then store3 else null end as 'price'
  from Products
) t where t.store is not null;

（2）按照标准模板套入（推荐）

select product_id, 'store1' as store, store1 as price
from Products where store1 is not null
union all
select product_id, 'store2' as store, store2 as price
from Products where store2 is not null
union all
select product_id, 'store3' as store, store3 as price
from Products where store3 is not null;

相应的，如果遇到列转行怎么处理呢？如果将题目的输入与输出颠倒，即：

#输入：
Products table:
+------------+--------+-------+
| product_id | store  | price |
+------------+--------+-------+
| 0          | store1 | 95    |
| 0          | store2 | 100   |
| 0          | store3 | 105   |
| 1          | store1 | 70    |
| 1          | store3 | 80    |
+------------+--------+-------+
#输入：
+------------+--------+--------+--------+
| product_id | store1 | store2 | store3 |
+------------+--------+--------+--------+
| 0          | 95     | 100    | 105    |
| 1          | 70     | null   | 80     |
+------------+--------+--------+--------+

按照要求，对于SQL可写为如下形式：

SELECT product_id
  ,MAX(CASE WHEN store = 'store1' THEN price ELSE null END) as 'store1'
  ,MAX(CASE WHEN store = 'store2' THEN price ELSE null END) as 'store2'
  ,MAX(CASE WHEN store = 'store3' THEN price ELSE null END) as 'store3'
FROM Products
GROUP BY product_id;

无论是行转列，还是列转行，需要了解sql语句在每一行执行了那些逻辑，提取相应字段数值。注意到他们输入输出的字段数都是不一致的，因此在这个过程中 union all 是至关重要的。采用 Max() 函数，是为了若存在多个相同product_id，取其中的最大值。

608.树节点

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

#Tree Table
+----+------+
| id | p_id |
+----+------+
| 1  | null |
| 2  | 1    |
| 3  | 1    |
| 4  | 2    |
| 5  | 2    |
+----+------+
#输出
+----+------+
| id | Type |
+----+------+
| 1  | Root |
| 2  | Inner|
| 3  | Leaf |
| 4  | Leaf |
| 5  | Leaf |
+----+------+

树中每个节点属于以下三种类型之一：
（1）叶子：如果这个节点没有任何孩子节点。
（2）根：如果这个节点是整棵树的根，即没有父节点。
（3）内部节点：如果这个节点既不是叶子节点也不是根节点。
写一个查询语句，输出所有节点的编号和节点的类型，并将结果按照节点编号排序。
我在思考这道题时，题意的判断条件是根据 id 与 p_id 之间的关系，想到了自关联的解决方法。将 Tree 中 id 与 p_id 作为外关联条件，考虑 id 或 p_id 为 null 时的情形，判断为 Leaf，Root 或 Inner。（复杂）

select distinct id
  ,case when p_id is null then 'Root' else Type end as 'Type'
from (
select id 
  ,p_id
  ,case when p_id is not null and p_id2 is not null then 'Inner' else 'Leaf' end as 'Type' 
from(
select t1.id, t1.p_id, t2.p_id as 'p_id2'
  from tree t1 left join tree t2 on t1.id = t2.p_id
) tmp
) tmp2 order by id;

官方一共给出3种解决方法，但是我更倾向与第二种和第三种，习惯使用流程控制语句case…when…end 或 if() 可能更符合程序员思维，哈哈。
（1）case when 方法（推荐）

SELECT
    id AS `Id`,
    CASE
        WHEN tree.id = (SELECT atree.id FROM tree atree WHERE atree.p_id IS NULL)
          THEN 'Root'
        WHEN tree.id IN (SELECT atree.p_id FROM tree atree)
          THEN 'Inner'
        ELSE 'Leaf'
    END AS Type
FROM
    tree
ORDER BY `Id`;

（2）if() 重写 case when 方法

SELECT
    atree.id,
    IF(ISNULL(atree.p_id),
        'Root',
        IF(atree.id IN (SELECT p_id FROM tree), 'Inner','Leaf')) Type
FROM
    tree atree
ORDER BY atree.id;

详见：https://leetcode.cn/problems/tree-node/solution/shu-jie-dian-by-leetcode/

176.第二高的薪水

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

#输入：
+----+--------+
| id | salary |
+----+--------+
| 1  | 100    |
+----+--------+
#输出：
+---------------------+
| SecondHighestSalary |
+---------------------+
| null                |
+---------------------+

编写一个 SQL 查询，获取并返回 Employee 表中第二高的薪水 。如果不存在第二高的薪水，查询应该返回 null。
这个直接套模板就行，去重，倒序排序，限制查询结果，偏移 t 位数（即第 t+1 位），最后通过 ifnull() 函数判断空值。

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

–不积硅步，无以至千里；不积小流，无以成江海
–坚持每天学一小会儿，共勉！