37 - 43 题子查询 - 高频 SQL 50 题基础版

1. 相关知识点

• 暂无

2. 例子

2.37 - 上级经理已离职的公司员工

select
    e1.employee_id 
from
    Employees e1 left join Employees e2 
on 
    e1.manager_id =e2.employee_id
where
    e2.manager_id is null and e1.manager_id is not null and e1.salary<30000;

2.38 - 换座位

-- 方法一
select
    (
    case
        when id%2=1 and id=(select max(id) from seat) then id
        when id%2=0 then id-1
        else id+1
    end) as id, student
from
    seat
order by
    id;

-- 方法二

select
    if(id%2=0,id-1,if(id=(select max(id) from Seat),id,id+1)) as id,student
from
    Seat
order by 
    id;

2.39 - 电影评分

(select
    u.name as results
from
    MovieRating m left join Users u on m.user_id=u.user_id 
GROUP BY
    m.user_id
order by 
    count(*) desc,u.name asc limit 1)
union all
(select
    m1.title as results
from
    MovieRating m left join Movies m1  on m.movie_id=m1.movie_id    
where
    month(m.created_at)=2
group by
    m.movie_id
order by 
    avg(m.rating) desc,m1.title asc
limit 1);

2.40 - 餐馆营业额变化增长

-- 方法一
SELECT 
    t.visited_on,
    sum(c.amount) amount,
    ROUND(sum(c.amount) / 7, 2) average_amount
FROM 
    customer c,
    -- 查出足够7天的数据日期
     (SELECT DISTINCT 
        visited_on
      FROM 
        customer
      WHERE 
        visited_on >=(SELECT ADDDATE(MIN(visited_on), 6) FROM customer)) t
WHERE 
    c.visited_on BETWEEN SUBDATE(t.visited_on, 6) AND t.visited_on
GROUP BY 
    t.visited_on;


-- 方法二
SELECT visited_on,
       amount,
      round(amount / 7, 2) average_amount
FROM (
        SELECT DISTINCT 
            visited_on,
            SUM(amount) OVER (ORDER BY visited_on RANGE INTERVAL 6 DAY PRECEDING) amount
        FROM 
            customer
    ) t
WHERE 
    -- 当前日期比最小值多6天及以上
    visited_on >=(SELECT ADDDATE(MIN(visited_on), 6) FROM customer);

-- 方法三
select 
    visited_on,sum_amount as amount,round(average_amount,2) as average_amount 
from
    # 分组的数据进行7日分组
    (
        select
            visited_on,
            sum(amount) over (order by visited_on rows 6 preceding) sum_amount,
            avg(amount) over (order by visited_on rows 6 preceding) average_amount
        from
        # 根据日期分组
            (
                select
                    visited_on,sum(amount) as amount
                from    
                    Customer
                group by    
                    visited_on
            )t1
    )t2
where datediff(visited_on,(select min(visited_on) from Customer))>=6

2.41 - 好友申请 II ：谁有最多的好友

select
    *,count(*) num
from
    (
        select 
            requester_id as id 
        from
            RequestAccepted 
        union all
        select
            accepter_id as id
        from
            RequestAccepted
    ) t
group by 
    id
order by num desc
limit 1;

2.42 - 2016年的投资

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

    (lat,lon) not in (
        select
            lat,lon 
        from 
            Insurance 
        group by
            lat,lon
        having 
            count(*)>1
    )

2.43 - 部门工资前三高的所有员工

# dense_rank 排名

select
    Department,
    Employee,
    Salary
from
    (
        select
            d.name as Department,
            e.name as Employee,
            e.salary as Salary,
            (dense_rank() over (partition by d.name order by e.salary desc)) as ranking
        from 
            Employee e left join Department d on e.departmentId=d.id
    ) as t
where 
    ranking<=3;