ps:文章只是个人随笔,记录一下刷题时候容易忘记的知识点。
题目:LeetCode 1795. 每个产品在不同商店的价格
select product_id,'store1' store, store1 price from Products where store1 is not null
union all
select product_id,'store2', store2 from Products where store2 is not null
union all
select product_id,'store3', store3 from Products where store3 is not null
知识点总结:
- 行转列,单独查询每列的数据,然后组合起来就是结果。其中union all不去重,union去重。
扩展:
- 列转行使用SUM/MAX + Case when + Group By。Max用来去重
题目:LeetCode 608. 树节点
select id,
case when p_id is null then 'Root'
when id in(select a.id as id
from Tree a,Tree b
where a.id = b.p_id
) then 'Inner'
else 'Leaf'
end as 'type'
from Tree
order by id;
#官方解法
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`
;
# LeetCode官方解法三,使用if(a,b,c)实现,没想到,记录一下。
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
知识点总结:
- Case When的简单应用。当然也可以用union,把三种情况都写出来,然后求并集。
题目:LeetCode 176. 第二高的薪水
select ifNULL(
(select distinct salary
from employee
order by salary desc limit 1,1),NULL
) as SecondHighestSalary
知识点总结:
- 使用limit控制输出,插件用多了,都快忘了还有limit。limit a,b ---->从a开始,取b个数。其中第一个a为0。即1,1是从第二条记录开始,取一条,相当于是取第二条。
- ifNULL(a, b)。a不为空取a,为空取b。
扩展:本题使用子查询也可以实现。需要考虑不存在第二高薪水的情况,因此可以把子查询结果变成临时表,就可以避免这个问题。上面贴出来的解法实际上和子查询很像了。