力扣SQL刷题2

571. 给定数字的频率查询中位数

题型：给出数值和对应频率，返回中位数
解答：分别升序和降序排列。中位数正序和倒序所处的位置都要大于等于（考虑到奇数）所有元素的一半
中位数应满足：其前面所有数的数量小于总数一半，其后面所有数的数量也小于总数一半

题目：

先新增排序列，再筛选，最后可能也不只有一个满足要求，所以求avg

select round(avg(num),1) as median
from
(select a.*,sum(frequency)over(order by num) as r1,
sum(frequency)over(order by num desc) as r2,
sum(frequency)over() as r3
from Numbers  as a) b
where r1>=r3/2 and r2>=r3/2

574. 当选者

题型：输出计数最多的值
解答：group by 、order by count(id) desc 和limit 1 的合作
因为先group by 再order（按count计数后排序） 再limit

题目：

select Candidate.name
from
(select candidateId
from Vote
group by candidateId
order by count(id) desc
limit 1 ) a
join Candidate 
on a.CandidateId=Candidate.id

608. 树节点

题型：新增一列，对不同情况输出不同值
解答：通常case when 能做的，用（selelct where）union （selelct where）或者if 都可以试试求解

题目：

法一：不同情况分别取出后，再union结合-思路简单，过程复杂
注意：in ---- not in
is null ----- is not null (是非空)

select id,"Root" Type
from tree
where p_id is null

union

select id,"Inner" Type
from tree
where  p_id is not null and id in (select distinct a.p_id from tree a where p_id is not null)

union

select id,"Leaf" Type
from tree
where id  not in (select distinct a.p_id from tree a where p_id  is not null) and  p_id is not null

法二：case when
注意：任何与null值的对比都将返回null ，是 任何

select if(1 = null, 'true', 'false')  ----返回false
select if(1 != null, 'true', 'false')  ----返回false

所以下面的输出中，本该是"Leaf"的全变成了"Inner" 因为t1.id not in （中有null值）返回一定为false

select id,
(case when t1.id in (select b.id from tree b where b.p_id is null ) then "Root"
when t1.id not in (select distinct a.p_id from tree a) then "Leaf"
else "Inner" end )  Type
from tree t1 

下面两个代码都正确

select id,
(case when t1.id in (select b.id from tree b where b.p_id is null ) then "Root"
when t1.id not in (select distinct a.p_id from tree a where a.p_id is not null) then "Leaf"
else "Inner" end )  Type
from tree t1 

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`;

法三：if(要满足的条件,满足时的值，不满足时的值)
if的嵌套

select id,
if (t1.id in (select b.id from tree b where b.p_id is null ) ,"Root",
     (if(t1.id in (select distinct a.p_id from tree a ),"Inner", "Leaf"))
)  Type
from tree t1 

612. 平面上的最近距离

题型：每一行如何与其他行进行连接
解答：自连接然后where筛选

题目：


注意：两列不全等 — (p1.x,p1.y)<>(p2.x,p2.y) —(p1.x != p2.x) or (p1.y != p2.y)
平方 — pow(x,2)
开平方 ---- sqrt()
保留小数点 — round(x,2)

select  round(min(sqrt(pow(p1.x-p2.x,2)+pow(p1.y-p2.y,2))),2)  shortest
from Point2D p1 join Point2D p2 
on (p1.x,p1.y)<>(p2.x,p2.y)

如何优化？ — 比较时任意两个数重复比较了两次

select  round(min(sqrt(pow(p1.x-p2.x,2)+pow(p1.y-p2.y,2))),2)  shortest
from Point2D p1 join Point2D p2 
on (p1.x>p2.x ) or(p1.x =p2.x and p1.y > p2.y )

619. 只出现一次的最大数字

题型：空变成null输出
解答：在内层select上套一层select，就可以自动在找不到的时候返回null
空表示表格没有任何输出，null表示输出为null

题目：

select
(select num
from MyNumbers
group by num
having count(1)=1
order by  num desc
limit 1 ) as num