建立公用表 With

建立公用表 With

1. 介绍

with as 子查询仅执行一次，将结果存储在用户临时表中，提高查询性能，所以适合多次引用的场景

# Write your MySQL query statement below
# 找出那些既没有最多，也没有最少参与者的活动的名字
# Activities 表中的任意活动都有在 Friends 中参与


with a as (select activity, 
count(*) amount
from friends 
group by activity)

select activity
from a 
where amount > (select min(amount) from a )
and amount < (select max(amount) from a)

2. 多个临时表建立在 with 中，用逗号隔开

--- write your code here ---
with a as
(select distinct sender_id, send_to_id, requester_id, accepter_id from FriendRequest f, RequestAccept r where
    f.sender_id = r.requester_id and f.send_to_id = r.accepter_id and requester_id is not null),
b as
(select distinct sender_id, send_to_id from FriendRequest)

select round(
    (select count(1) from a) / (select count(1) from b), 2
) accept_rate