LeetCode数据库SQL练习（四）

YBCarry_段松啓
386 阅读9分钟

好友申请系列

在 Facebook 或者 Twitter 这样的社交应用中，人们经常会发好友申请也会收到其他人的好友申请。
表：FriendRequest

+----------------+---------+
| Column Name    | Type    |
+----------------+---------+
| sender_id      | int     |
| send_to_id     | int     |
| request_date   | date    |
+----------------+---------+
此表没有主键，它可能包含重复项。
该表包含发送请求的用户的 ID ，接受请求的用户的 ID 以及请求的日期。

表：RequestAccepted

+----------------+---------+
| Column Name    | Type    |
+----------------+---------+
| requester_id   | int     |
| accepter_id    | int     |
| accept_date    | date    |
+----------------+---------+
此表没有主键，它可能包含重复项。
该表包含发送请求的用户的 ID ，接受请求的用户的 ID 以及请求通过的日期。

1、总体通过率
写一个查询语句，求出好友申请的通过率，用 2 位小数表示。通过率由接受好友申请的数目除以申请总数。

提示：

  • 通过的好友申请不一定都在表friend_request中。你只需要统计总的被通过的申请数（不管它们在不在表FriendRequest中），并将它除以申请总数，得到通过率
  • 一个好友申请发送者有可能会给接受者发几条好友申请，也有可能一个好友申请会被通过好几次。这种情况下，重复的好友申请只统计一次。
  • 如果一个好友申请都没有，通过率为 0.00 。

查询结果应该如下例所示：

FriendRequest 表：
+-----------+------------+--------------+
| sender_id | send_to_id | request_date |
+-----------+------------+--------------+
| 1         | 2          | 2016/06/01   |
| 1         | 3          | 2016/06/01   |
| 1         | 4          | 2016/06/01   |
| 2         | 3          | 2016/06/02   |
| 3         | 4          | 2016/06/09   |
+-----------+------------+--------------+

RequestAccepted 表：
+--------------+-------------+-------------+
| requester_id | accepter_id | accept_date |
+--------------+-------------+-------------+
| 1            | 2           | 2016/06/03  |
| 1            | 3           | 2016/06/08  |
| 2            | 3           | 2016/06/08  |
| 3            | 4           | 2016/06/09  |
| 3            | 4           | 2016/06/10  |
+--------------+-------------+-------------+

Result 表：
+-------------+
| accept_rate |
+-------------+
| 0.8         |
+-------------+
总共有 5 个请求，有 4 个不同的通过请求，所以通过率是 0.80

进阶:

  • 你能写一个查询语句得到每个月的通过率吗？

  • 你能求出每一天的累计通过率吗？

  • SQL：

    • select 
round(ifnull((select count(distinct requester_id, accepter_id) from RequestAccepted ) /
(select count(distinct sender_id, send_to_id) from FriendRequest) ,0),2) 
accept_rate

2、谁有最多的好友 写一个查询语句，在RequestAccepted表中找出拥有最多的好友的人和他拥有的好友数目。
生成的测试用例保证拥有最多好友数目的只有 1 个人。
查询结果格式如下例所示。

示例：

输入：
RequestAccepted 表：
+--------------+-------------+-------------+
| requester_id | accepter_id | accept_date |
+--------------+-------------+-------------+
| 1            | 2           | 2016/06/03  |
| 1            | 3           | 2016/06/08  |
| 2            | 3           | 2016/06/08  |
| 3            | 4           | 2016/06/09  |
+--------------+-------------+-------------+
输出：
+----+-----+
| id | num |
+----+-----+
| 3  | 3   |
+----+-----+
解释：
编号为 3 的人是编号为 1 ，2 和 4 的人的好友，所以他总共有 3 个好友，比其他人都多。

注意：

  • 保证拥有最多好友数目的只有 1 个人。
  • 好友申请只会被接受一次，所以不会有 requester_id 和 accepter_id 值都相同的重复记录。

进阶： 在真实世界里，可能会有多个人拥有好友数相同且最多，你能找到所有这些人吗？

  • union和union all：

    • union all对多个表的查询合并在一个结果集合中显示（列名要相同）
    • union是对union all进行去重
    • union all效率高于union

  • SQL：

    • select
t2.id id, t2.num num
from
    (select
    t1.id, sum(t1.num) num
    from
        (select
        requester_id id,
        count(accepter_id) num
        from
        RequestAccepted
        group by requester_id
        union all
        select
        accepter_id id,
        count(requester_id) num
        from
        RequestAccepted
        group by accepter_id) t1
    group by id) t2
order by t2.num DESC
limit 1

体育馆的人流量

表：Stadium

+---------------+---------+
| Column Name   | Type    |
+---------------+---------+
| id            | int     |
| visit_date    | date    |
| people        | int     |
+---------------+---------+
visit_date 是表的主键
每日人流量信息被记录在这三列信息中：序号 (id)、日期 (visit_date)、人流量 (people)
每天只有一行记录，日期随着 id 的增加而增加

编写一个 SQL 查询以找出每行的人数大于或等于 100 且 id 连续的三行或更多行记录。
返回按 visit_date 升序排列的结果表。

查询结果格式如下所示。

Stadium table:
+------+------------+-----------+
| id   | visit_date | people    |
+------+------------+-----------+
| 1    | 2017-01-01 | 10        |
| 2    | 2017-01-02 | 109       |
| 3    | 2017-01-03 | 150       |
| 4    | 2017-01-04 | 99        |
| 5    | 2017-01-05 | 145       |
| 6    | 2017-01-06 | 1455      |
| 7    | 2017-01-07 | 199       |
| 8    | 2017-01-09 | 188       |
+------+------------+-----------+

Result table:
+------+------------+-----------+
| id   | visit_date | people    |
+------+------------+-----------+
| 5    | 2017-01-05 | 145       |
| 6    | 2017-01-06 | 1455      |
| 7    | 2017-01-07 | 199       |
| 8    | 2017-01-09 | 188       |
+------+------------+-----------+

解释： 
id 为 5、6、7、8 的四行 id 连续，并且每行都有 >= 100 的人数记录。
请注意，即使第 7 行和第 8 行的 visit_date 不是连续的，输出也应当包含第 8 行，因为我们只需要考虑 id 连续的记录。
不输出 id 为 2 和 3 的行，因为至少需要三条 id 连续的记录。

  • 连续登录类型问题

  • SQL1：

    • select
id, visit_date, people
from
    (select
    id, visit_date, people,
    LEAD(people, 1, 0) over(order by id) le1,
    LEAD(people, 2, 0) over(order by id) le2,
    LAG(people, 1, 0) over(order by id) la1,
    LAG(people, 2, 0) over(order by id) la2
    from
    Stadium) t
where (people >= 100 and le1 >= 100 and la1 >= 100) or
    (people >= 100 and le1 >= 100 and le2 >= 100) or
    (people >= 100 and la1 >= 100 and la2 >= 100)

  • SQL2：

    • 窗口函数：
      • 连续 —— id减去排名，差值相同
    • select
t2.id, t2.visit_date, t2.people
from
    (select
    t1.id, t1.visit_date, t1.people,
    count(*) over(partition by rn) cnt
    from
        (select
        *,
        id - ROW_NUMBER() over(order by id) rn
        from
        Stadium
        where people >= 100) t1
    ) t2
where cnt >= 3
order by t2.id

连续空余座位

几个朋友来到电影院的售票处，准备预约连续空余座位。
你能利用表 cinema ，帮他们写一个查询语句，获取所有空余座位，并将它们按照 seat_id 排序后返回吗？

+-------------+------+
| Column Name | Type |
+-------------+------+
| seat_id     | int  |
| free        | bool |
+-------------+------+
Seat_id是该表的自动递增主键列。
该表的每一行表示第i个座位是否空闲。1表示空闲，0表示被占用。

编写一个SQL查询来报告电影院所有连续可用的座位。
返回按seat_id 升序排序 的结果表。
测试用例的生成使得两个以上的座位连续可用。
查询结果格式如下所示。

示例 1:

输入: 
Cinema 表:
+---------+------+
| seat_id | free |
+---------+------+
| 1       | 1    |
| 2       | 0    |
| 3       | 1    |
| 4       | 1    |
| 5       | 1    |
+---------+------+
输出: 
+---------+
| seat_id |
+---------+
| 3       |
| 4       |
| 5       |
+---------+

注意：

  • seat_id 字段是一个自增的整数，free 字段是布尔类型（'1' 表示空余， '0' 表示已被占据）。

  • 连续空余座位的定义是大于等于 2 个连续空余的座位。


  • SQL1：

    • select
distinct a.seat_id
from
cinema a join cinema b
on abs(a.seat_id - b.seat_id) = 1 and a.free = '1' and b.free = '1'
order by a.seat_id

  • 窗口函数：

  • SQL2：

    • select
t2.seat_id seat_id
from
    (select
    t1.seat_id,
    count(*) over(partition by t1.rk) cnt
    from
        (select
        seat_id,
        seat_id - ROW_NUMBER() over(order by seat_id) rk
        from
        cinema
        where free = '1') t1
    ) t2
where t2.cnt > 1
order by seat_id

  • SQL3：

    • with t as (
select
seat_id,
seat_id - row_number() over() as rk
from
cinema
where free = '1')

select
seat_id
from
t
where rk in (select
            rk
            from
            t
            group by rk
            having count(*) > 1)

销售员

描述
给定 3 个表： salesperson， company， orders。
输出所有表 salesperson 中，没有向公司 'RED' 销售任何东西的销售员。
示例：
输入
表： salesperson

+----------+------+--------+-----------------+-----------+
| sales_id | name | salary | commission_rate | hire_date |
+----------+------+--------+-----------------+-----------+
|   1      | John | 100000 |     6           | 4/1/2006  |
|   2      | Amy  | 120000 |     5           | 5/1/2010  |
|   3      | Mark | 65000  |     12          | 12/25/2008|
|   4      | Pam  | 25000  |     25          | 1/1/2005  |
|   5      | Alex | 50000  |     10          | 2/3/2007  |
+----------+------+--------+-----------------+-----------+

表 salesperson 存储了所有销售员的信息。每个销售员都有一个销售员编号 sales_id 和他的名字 name 。
表： company

+---------+--------+------------+
| com_id  |  name  |    city    |
+---------+--------+------------+
|   1     |  RED   |   Boston   |
|   2     | ORANGE |   New York |
|   3     | YELLOW |   Boston   |
|   4     | GREEN  |   Austin   |
+---------+--------+------------+

表 company 存储了所有公司的信息。每个公司都有一个公司编号 com_id 和它的名字 name 。
表： orders

+----------+------------+---------+----------+--------+
| order_id | order_date | com_id  | sales_id | amount |
+----------+------------+---------+----------+--------+
| 1        |   1/1/2014 |    3    |    4     | 100000 |
| 2        |   2/1/2014 |    4    |    5     | 5000   |
| 3        |   3/1/2014 |    1    |    1     | 50000  |
| 4        |   4/1/2014 |    1    |    4     | 25000  |
+----------+----------+---------+----------+--------+

表 orders 存储了所有的销售数据，包括销售员编号 sales_id 和公司编号 com_id 。
输出

+------+
| name | 
+------+
| Amy  | 
| Mark | 
| Alex |
+------+

解释

根据表 orders 中的订单 '3' 和 '4' ，容易看出只有 'John' 和 'Pam' 两个销售员曾经向公司 'RED' 销售过。
所以我们需要输出表 salesperson 中所有其他人的名字。

  • SQL1：

    • select
name
from
salesperson
where name not in (select
                    s.name
                    from
                    orders o join salesperson s
                    on o.sales_id = s.sales_id
                    join company c
                    on o.com_id = c.com_id
                    where c.name = 'RED')

  • SQL2：

    • select
name
from
salesperson
where sales_id not in (select
                        o.sales_id
                        from
                        orders o join company c
                        on o.com_id = c.com_id
                        where c.name = 'RED')

  • SQL3：

    • select
s.name name
from
salesperson s left join orders o
on s.sales_id = o.sales_id
left join company c
on o.com_id = c.com_id
group by s.name
having sum(if(c.name = 'RED', 1, 0)) = 0

树节点

给定一个表 tree，id 是树节点的编号， p_id 是它父节点的 id 。

+----+------+
| id | p_id |
+----+------+
| 1  | null |
| 2  | 1    |
| 3  | 1    |
| 4  | 2    |
| 5  | 2    |
+----+------+

树中每个节点属于以下三种类型之一：

  • 叶子：如果这个节点没有任何孩子节点。
  • 根：如果这个节点是整棵树的根，即没有父节点。
  • 内部节点：如果这个节点既不是叶子节点也不是根节点。

写一个查询语句，输出所有节点的编号和节点的类型，并将结果按照节点编号排序。上面样例的结果为：

+----+------+
| id | Type |
+----+------+
| 1  | Root |
| 2  | Inner|
| 3  | Leaf |
| 4  | Leaf |
| 5  | Leaf |
+----+------+

解释

  • 节点 '1' 是根节点，因为它的父节点是 NULL ，同时它有孩子节点 '2' 和 '3'。
  • 节点 '2' 是内部节点，因为它有父节点 '1' ，也有孩子节点 '4' 和 '5' 。
  • 节点 '3', '4' 和 '5' 都是叶子节点，因为它们都有父节点同时没有孩子节点。
  • 样例中树的形态如下： 
    			  1
			/   \
                      2       3
                    /   \
                  4       5

注意： 如果树中只有一个节点，你只需要输出它的根属性。

  • SQL1：

    • 合并三种节点类型
    • select
id,
'Root' Type
from
tree
where p_id is null
union all
select
id,
'Inner' Type
from
tree
where id in (select distinct p_id from tree where p_id is not null) and p_id is not null
union all
select
id,
'Leaf' 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

  • 根据条件判断：

  • SQL2：

    • select
id,
case
when p_id is null then 'Root'
when id in (select distinct p_id from tree) then 'Inner' else 'Leaf'
end Type
from
tree

  • SQL3：

    • select
id,
if(isnull(p_id), 'Root', if(id in (select distinct p_id from tree), 'Inner', 'Leaf')) type
from
tree

判断三角形

一个小学生 Tim 的作业是判断三条线段是否能形成一个三角形。 然而，这个作业非常繁重，因为有几百组线段需要判断。
假设表 triangle保存了所有三条线段的长度 x、y、z ，请你帮 Tim 写一个查询语句，来判断每组 x、y、z 是否可以组成一个三角形？
以 任意顺序 返回结果表。

+-------------+------+
| Column Name | Type |
+-------------+------+
| x           | int  |
| y           | int  |
| z           | int  |
+-------------+------+
(x, y, z)是该表的主键列。
该表的每一行包含三个线段的长度。

示例 1:

输入: 
Triangle 表:
+----+----+----+
| x  | y  | z  |
+----+----+----+
| 13 | 15 | 30 |
| 10 | 20 | 15 |
+----+----+----+
输出: 
+----+----+----+----------+
| x  | y  | z  | triangle |
+----+----+----+----------+
| 13 | 15 | 30 | No       |
| 10 | 20 | 15 | Yes      |
+----+----+----+----------+

  • POWER()函数：

    • 等于POW()函数，POWER(x, y)，返回x的y次方。

  • SQL1：

    • 任意两边之和大于第三边
    • select
x, y, z,
case when x + y > z and x + z > y and y + z > x then 'Yes' else 'No' end triangle
from
triangle

  • SQL2：

    • 余弦定理：
      • ABS((x^2 - y^2 - z^2)/2yz)计算结果是否在(0,1)
    • select
x, y, z,
if(abs((power(x, 2) - power(y, 2) - power(z, 2)) / (2 * y * z)) < 1, 'Yes', 'No') triangle
from
triangle

平面上的最近距离

Point2D 表：

+-------------+------+
| Column Name | Type |
+-------------+------+
| x           | int  |
| y           | int  |
+-------------+------+
(x, y) 是这张表的主键
这张表的每一行表示 X-Y 平面上一个点的位置

p1(x1, y1) 和 p2(x2, y2) 这两点之间的距离是 sqrt((x2 - x1)2 + (y2 - y1)2) 。
请你写一个 SQL 查询报告 Point2D 表中任意两点之间的最短距离。保留 2 位小数 。
查询结果格式如下例所示。

示例：

输入：
Point2D table:
+----+----+
| x  | y  |
+----+----+
| -1 | -1 |
| 0  | 0  |
| -1 | -2 |
+----+----+
输出：
+----------+
| shortest |
+----------+
| 1.00     |
+----------+
解释：最短距离是 1.00 ，从点 (-1, -1) 到点 (-1, 2) 。

注意： 任意点之间的最远距离小于 10000 。

  • SQL1： 
    • select
round(sqrt(power(t.x1 - t.x2, 2) + power(t.y1 - t.y2, 2)), 2) shortest
from
(select
a.x x1, a.y y1, b.x x2, b.y y2
from
Point2D a,
Point2D b
where not (a.x = b.x and a.y = b.y)) t
order by shortest
limit 1
  • 优化：
  • SQL2： 
    • select
round(min(sqrt(power(t1.x - t2.x, 2) + power(t1.y - t2.y, 2))), 2) shortest
from
Point2D t1 join Point2D t2
on t1.x < t2.x or (t1.x = t2.x and t1.y < t2.y)

直线上的最近距离

表 point 保存了一些点在 x 轴上的坐标，这些坐标都是整数。
写一个查询语句，找到这些点中最近两个点之间的距离。

+-----+
| x   |
|-----|
| -1  |
| 0   |
| 2   |
+-----+

最近距离显然是 '1' ，是点 '-1' 和 '0' 之间的距离。所以输出应该如下：

+---------+
| shortest|
|---------|
| 1       |
+---------+

注意： 每个点都与其他点坐标不同，表 table 不会有重复坐标出现。
进阶： 如果这些点在 x 轴上从左到右都有一个编号，输出结果时需要输出最近点对的编号呢？

  • SQL1：

    • select
min(abs(t1.x - t2.x)) shortest
from
point t1 join point t2
on t1.x < t2.x

  • 窗口函数：

  • SQL2：

    • select
min(abs(t1.x - t2.x)) shortest
from
(select x, row_number() over(order by x) rn from point) t1,
(select x, row_number() over(order by x) rn from point) t2
where t1.rn != t2.rn

  • SQL3：

    • select
x - lag(x) over(order by x) as shortest
from
point
order by shortest
limit 1, 1

二级关注者

在 facebook 中，表 follow 会有 2 个字段： followee, follower ，分别表示被关注者和关注者。
请写一个 sql 查询语句，对每一个关注者，查询关注他的关注者的数目。

比方说：

+-------------+------------+
| followee    | follower   |
+-------------+------------+
|     A       |     B      |
|     B       |     C      |
|     B       |     D      |
|     D       |     E      |
+-------------+------------+

应该输出：

+-------------+------------+
| follower    | num        |
+-------------+------------+
|     B       |  2         |
|     D       |  1         |
+-------------+------------+

解释： B 和 D 都在在 follower 字段中出现，作为被关注者，B 被 C 和 D 关注，D 被 E 关注。A 不在 follower 字段内，所以A不在输出列表中。

注意：

  • 被关注者永远不会被他 / 她自己关注。

  • 将结果按照字典序返回。


  • join

  • SQL1：

    • select
t1.follower, count(distinct t2.follower) num
from
follow t1 join follow t2
on t1.follower = t2.followee
group by t1.follower
order by t1.follower

  • 子查询

  • SQL2：

    • select
followee follower, count(distinct follower) num
from
follow
where followee in (select
                    follower
                    from
                    follow)
group by followee
order by follower
avatar
文章
阅读
粉丝