看了下今天的主题：组合查询和指定选取，感觉有点复杂。

1965. Employees With Missing Information

Analysis

找出缺失信息的数据，需要 2 个表一起组合查询。实际上，就是挑出 2 个表中只出现了 1 次的 employee_id。

Code

select employee_id
from (
    select employee_id from Employees
    union all
    select employee_id from Salaries
) as ans
group by employee_id
having count(employee_id) = 1
order by employee_id

1795. Rearrange Products Table

Analysis

这个题是需要将表的数据重新排列，有点像是在做行列转换。

Code

实际上是行列转换，然后再组合成一张表。

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 stroe, store3 as price
from Products
where store3 is not null

608. Tree Node

Analysis

SQL 与树的结合题？

Code

method 1

第一种方法是分别挑出 3 种不同的结点，然后组合到一起。

select id, 'Root' as type
from Tree
where p_id is null

union

select id, 'Leaf' as type
from Tree
where id not in (
    select distinct p_id
    from Tree
    where p_id is not null
) and p_id is not null

union

select id, 'Inner' as type
from Tree
where id in (
    select distinct p_id
    from tree
    where p_id is not null
) and p_id is not null

method 2

可以使用 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'

method 3

还可以使用 IF 函数：

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

176. Second Highest Salary

Analysis

题意很简单，找出第二小的值。怎么这种题，一道数据库了，就感觉这么难做呢？

Code

method 1

可以使用 LIMIT OFFSET 来完成取第二的操作。但是，如果只有一个数据，结果就是 NULL 了。所以，需要将第一个表作为临时表。

select (
    select distinct salary
    from Employee
    order by salary desc
    limit 1 offset 1
    ) as SecondHighestSalary

method 2

也可以使用 IFNULL 直接返回 NULL：

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

Summary

怎么感觉有点难呢？还是用的少了。