2019.12.12 leetcode SQL

580 统计各专业学生人数

SELECT
    dept_name, COUNT(student_id) AS student_number
FROM
    department
        left OUTER JOIN
    student ON department.dept_id = student.dept_id
GROUP BY department.dept_name
ORDER BY student_number DESC , department.dept_name

这里注意 LEFT OUTER JOIN
OUTER JOIN 是行不通的哈，老是报错
改为LFET OUTER JOIN 就可以了
LEFT OUTER JOIN 会有NULL

585 2016年的两种投资

select sum(A.TIV_2016) as `TIV_2016`
from insurance as A
where exists(
    select *
    from insurance as B
    where B.PID != A.PID and B.TIV_2015 = A.TIV_2015 
) and not exists(
    select *
    from insurance as B
    where B.PID != A.PID and B.LAT = A.LAT and B.LON = A.LON
)

作者：jason-2
链接：https://leetcode-cn.com/problems/investments-in-2016/solution/liang-chong-jie-fa-by-jason-2-5/
来源：力扣（LeetCode）
著作权归作者所有。商业转载请联系作者获得授权，非商业转载请注明出处。

学会了EXISTS 用法，以前木有用过

SELECT round(sum(DISTINCT i1.TIV_2016),2) as TIV_2016
FROM insurance i1, insurance i2
WHERE i1.TIV_2015 = i2.TIV_2015 AND i1.PID!=i2.PID
AND i1.PID in
(SELECT i3.PID AS id
FROM insurance i3
GROUP BY i3.LAT,i3.LON HAVING COUNT(*)=1);

作者：chriswong94
链接：https://leetcode-cn.com/problems/investments-in-2016/solution/jian-dan-si-lu-by-chriswong94/
来源：力扣（LeetCode）
著作权归作者所有。商业转载请联系作者获得授权，非商业转载请注明出处。

这一种效率高一些

602 谁拥有最多的好友

Select id,count(*) AS num
FROM (
SELECT requester_id as id
From request_accepted 
Union all 
SELECT accepter_id
From request_accepted) s
Group by id
Order by num DESC LIMIT 1

第一次用UNION ALL
UNION ALL : 不会删除重复项哦
UNION : 会删除重复项

608 树节点

# Write your MySQL query statement 
Select s2.id,if(s2.parent_num = 0,'Root',if(s2.child_num = 0,'Leaf','Inner')) as Type
FROM (
Select s.id, count(s.p_id) as parent_num,count(s.c_id) as child_num
From 
(
Select t1.id,t1.p_id,t2.id as c_id
From tree t1
LEFT JOIN tree t2
ON t1.id = t2.p_id
) s
Group by s.id )  S2

第一次独立运用LEFT JOIN
效率有待提升77%

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
ORDER BY id;

作者：LeetCode
链接：https://leetcode-cn.com/problems/tree-node/solution/shu-jie-dian-by-leetcode/
来源：力扣（LeetCode）
著作权归作者所有。商业转载请联系作者获得授权，非商业转载请注明出处。

官方解答：
（1）运用UNION
（2）原来’Root’ AS TYPE 还有这种操作，学到了

Select min(ROUND(sqrt((s.p2_x - s.p1_x)^2 + (s.p2_y - s.p1_y)^2),2)) as shortest
From (
Select p1.x as p1_x, p1.y as p1_y,p2.x as p2_x,p2.y as p2_y
From point_2d p1, point_2d p2 
WHERE p1.x != p2.x or p1.y != p2.y
) s 

这个是我一直过不了的源程序
他一直报错 BIGINT UNSIGNED VALUE

SELECT
    ROUND(SQRT(MIN((POW(p1.x - p2.x, 2) + POW(p1.y - p2.y, 2)))), 2) AS shortest
FROM
    point_2d p1
        JOIN
    point_2d p2 ON p1.x != p2.x OR p1.y != p2.y
;

作者：LeetCode
链接：https://leetcode-cn.com/problems/shortest-distance-in-a-plane/solution/ping-mian-shang-de-zui-jin-ju-chi-by-leetcode/
来源：力扣（LeetCode）
著作权归作者所有。商业转载请联系作者获得授权，非商业转载请注明出处。

题解里把MIN换了个位置。。。。
好吧，学到了调BUG方式