LeetCode 高频 SQL 50 题（基础版）之 【子查询】· 下

上部分：LeetCode 高频 SQL 50 题（基础版）之 【子查询】· 上

题目：1321. 餐馆营业额变化增长

题解：

select t2.visited_on visited_on,t2.amount amount,
round(amount/7,2) average_amount
from(
    select visited_on,
    sum(amount) over (order by visited_on rows 6 preceding ) amount
    from (
        select visited_on,sum(amount) amount from Customer
        group by visited_on 
    ) t1
) t2
where datediff(t2.visited_on,(select min(visited_on) from Customer))>=6
order by t2.visited_on

题目：602. 好友申请 II ：谁有最多的好友

题解：

select id,count(id) num from 
(
    select requester_id id from RequestAccepted
    union all
    select accepter_id id from RequestAccepted
) t1
group by id
order by count(id) desc
limit 1

题目：585. 2016年的投资

题解：

select round(sum(tiv_2016),2) tiv_2016 from Insurance
where tiv_2015 in (
    select tiv_2015 from Insurance
    group by tiv_2015
    having count(*)>1
)
and concat(lat,lon) in (
    select concat(lat,lon) as latlon from Insurance
    group by lat,lon
    having count(*)=1
)

题目：185. 部门工资前三高的所有员工

题解：

select d.name Department,e1.name Employee,e1.salary Salary from
Employee e1,Department d
where e1.departmentId=d.id 
and 3 > (
    select count(distinct e2.salary) from Employee e2
    where e1.departmentId=e2.departmentId 
    and e1.salary<e2.salary 
)
order by e1.departmentId,salary desc