力扣每日SQL刷题(607,608,612,613,614)

力扣_607

说明

点击跳转

分析

1.找出公司名为redde公司编号com_id;
2.根据上步找到的编号,关联查询与之相关的订单;

实现

准备工作

Create table If Not Exists SalesPerson (sales_id int, name varchar(255), salary int, commission_rate int, hire_date date);
Create table If Not Exists Company (com_id int, name varchar(255), city varchar(255));
Create table If Not Exists Orders (order_id int, order_date date, com_id int, sales_id int, amount int);
Truncate table SalesPerson;
insert into SalesPerson (sales_id, name, salary, commission_rate, hire_date) values ('1', 'John', '100000', '6', '2006/4/1');
insert into SalesPerson (sales_id, name, salary, commission_rate, hire_date) values ('2', 'Amy', '12000', '5', '2010/5/1');
insert into SalesPerson (sales_id, name, salary, commission_rate, hire_date) values ('3', 'Mark', '65000', '12', '2008/12/25');
insert into SalesPerson (sales_id, name, salary, commission_rate, hire_date) values ('4', 'Pam', '25000', '25', '2005/1/1');
insert into SalesPerson (sales_id, name, salary, commission_rate, hire_date) values ('5', 'Alex', '5000', '10', '2007/2/3');
Truncate table Company;
insert into Company (com_id, name, city) values ('1', 'RED', 'Boston');
insert into Company (com_id, name, city) values ('2', 'ORANGE', 'New York');
insert into Company (com_id, name, city) values ('3', 'YELLOW', 'Boston');
insert into Company (com_id, name, city) values ('4', 'GREEN', 'Austin');
Truncate table Orders;
insert into Orders (order_id, order_date, com_id, sales_id, amount) values ('1', '2014/1/1', '3', '4', '10000');
insert into Orders (order_id, order_date, com_id, sales_id, amount) values ('2', '2014/2/1', '4', '5', '5000');
insert into Orders (order_id, order_date, com_id, sales_id, amount) values ('3', '2014/3/1', '1', '1', '50000');
insert into Orders (order_id, order_date, com_id, sales_id, amount) values ('4', '2014/4/1', '1', '4', '25000');

问题

– 编写解决方案，找出没有任何与名为 “RED” 的公司相关的订单的所有销售人员的姓名

实现方法

with t1 as (select distinct sales_id
            from company c,
                 orders o
            where o.com_id = (select com_id from company where name = 'Red'))
select name
from salesperson
where sales_id not in (select t1.sales_id from t1);

小结

子查询
聚合函数

力扣_608

说明

点击跳转

分析

本题难点在于弄清楚表中各字段的意思:
id就是tree的每个结点,pid则表示该节点的父节点;
若pid为null,则该节点为根节点root;
若id没有与之对应的pid,则该节点为叶子节点;

弄清楚关系后,思路很简单:使用case when根据不同情况判断值即可.

实现

准备工作

Create table If Not Exists Tree
(
    id   int,
    p_id int
);
Truncate table Tree;
insert into Tree (id, p_id)
values ('1', null);
insert into Tree (id, p_id)
values ('2', '1');
insert into Tree (id, p_id)
values ('3', '1');
insert into Tree (id, p_id)
values ('4', '2');
insert into Tree (id, p_id)
values ('5', '2');

问题

– 编写一个解决方案来报告树中每个节点的类型

实现方法

select id,
       (case
            when p_id is null then 'Root'
            when id not in (select p_id from tree where p_id is not null) then 'Leaf'
            else 'Inner'
           end
           ) as type
from tree;

小结

case when的用法

力扣_612

说明

点击跳转

分析

本题难点在于如何拿到两个点进行计算,因为只有一张表.
由于需要找出两点之间最短的距离,所以需要每两个点都要计算.
由此就想到了笛卡尔积,用子链接的办法得到每两个点,然后计算.

实现

准备工作

Create Table If Not Exists Point2D (x int not null, y int not null);
Truncate table Point2D;
insert into Point2D (x, y) values ('-1', '-1');
insert into Point2D (x, y) values ('0', '0');
insert into Point2D (x, y) values ('-1', '-2');

问题

– p1(x1, y1) 和 p2(x2, y2) 这两点之间的距离是 sqrt((x2 - x1)2 + (y2 - y1)2) 。

– 编写解决方案，报告 Point2D 表中任意两点之间的最短距离。保留 2 位小数

实现方法

with t1 as (
    select sqrt(pow(p1.x - p2.x, 2) + pow(p1.y - p2.y, 2)) as shortest
from point2d p1,
     point2d p2
)
select round(shortest,2) as shortest
from t1
where shortest != 0
order by shortest
limit 1;

小结

sqrt():开方函数
pow():幂函数
round():四舍五入保留小数

力扣_613

说明

点击跳转

分析

思路与612题一样.

实现

准备工作

Create Table If Not Exists Point (x int not null)
Truncate table Point
insert into Point (x) values ('-1')
insert into Point (x) values ('0')
insert into Point (x) values ('2')

问题

– 找到 Point 表中任意两点之间的最短距离

实现方法

select min(abs(p1.x - p2.x)) as shortest
from Point as p1, Point as p2
where p1.x != p2.x

小结

abs():求绝对值函数

力扣_614

说明

点击跳转

分析

1.首先按照followee分组,统计数量;
2.然后按照follower分组,统计数量;
3.将两张表连接即可.

实现

准备工作

Create table If Not Exists Follow (followee varchar(255), follower varchar(255));
Truncate table Follow;
insert into Follow (followee, follower) values ('Alice', 'Bob');
insert into Follow (followee, follower) values ('Bob', 'Cena');
insert into Follow (followee, follower) values ('Bob', 'Donald');
insert into Follow (followee, follower) values ('Donald', 'Edward');

问题

– 编写一个解决方案来报告 二级用户 及其关注者的数量。
– 返回按 follower 字典序排序 的结果表

实现方法

with t1 as (select followee, count(follower) cnt1
            from follow
            group by followee)
   , t2 as (select follower, count(followee) cnt2
            from follow
            group by follower)
select follower,cnt1 as num
from t2 left join t1 on t2.follower = t1.followee
where followee is not null
order by follower;

小结

子查询
聚合函数