30-36题高级查询和连接 - 高频 SQL 50 题基础版

1. 相关知识点

• 相关函数

函数	含义
开窗函数lead(col,n)	统计窗口内往下第n行值
over(partition by xxx order by aaa)	按照xxx分组，按照aaa排序
row_number()	生成连续的序号，不考虑分数相同

2. 例子

2.30 - 每位经理的下属员工数量

-- 根据reports_to ，获取employee_id,即分组用e1.reports_to，查询用e2.employee_id,e2.name

select 
    e2.employee_id,e2.name ,count(e1.reports_to) reports_count,round(avg(e1.age),0) average_age
from 
    Employees e1 left join Employees e2
on
    e1.reports_to = e2.employee_id
where
    e1.reports_to is not null
group by 
    e1.reports_to;

2.31 - 员工的直属部门

-- and连接条件：e2.primary_flag='Y'的数据，其他的不连接,把e2.primary_flag='Y'的数据筛选出来
-- 左表一条数据的，默认为N的与右边连接不了，默认为null，根据右表e2.employee_id is null把单挑数据筛选出来
-- 右表只剩下为Y的数据，及将左表数据也筛选出为Y的e1.primary_flag='Y'，即e1.primary_flag='Y'
select 
    e1.employee_id,e1.department_id
from
    Employee e1 left join Employee e2
on
    e1.employee_id =e2.employee_id
and
    e2.primary_flag ='Y'
where
    e2.employee_id is null or e1.primary_flag='Y';

2.32 - 判断三角形

select 
    *,if(x+y>z and x+z>y and z+y > x,'Yes','No') triangle 
from
    Triangle;

2.33 - 连续出现的数字

-- 开窗函数lead(col,n) 统计窗口内往下第n行值
-- over(partition by xxx) 按照xxx所有行进行分组
-- over(partition by xxx order by aaa) 按照xxx分组，按照aaa排序

select distinct num as ConsecutiveNums 
from(
    select 
        num,
        # 从当前记录获取前一行记录的num值，如果没有前一行，则返回null
        lead(num,1) over(order by id) as a,
        # 从当前记录获取后一行记录的num值，如果没有后一行，则返回null
        lead(num,2) over(order by id) as b
    from
        logs
) t
where
    num=a and num=b;

2.34 - 指定日期的产品价格

-- row_number(行号) 生成连续的序号，不考虑分数相同
-- 在'2019-08-16'之前改的价格，使用最近一期的日期，没有在'2019-08-16'之前改的价格，默认价格为10
select 
    t.product_id, 
    t.new_price as price
from 
    (
        select 
            *,
            row_number() over (PARTITION BY product_id order by change_date desc) as row_num
        from 
            Products
        where 
            change_date<='2019-08-16') 
    as t
where 
    t.row_num=1
union
-- 没有在'2019-08-16'之前改的价格，默认价格为10
select 
    product_id, 10 as price 
from 
    Products 
group by 
    product_id
having 
    min(change_date)>'2019-08-16';

2.35 - 最后一个能进入巴士的人

-- sum(weight) over(order by turn) as total,根据turn升序，再求前面数的和
select
    person_name 
from
    (
        select
            person_name,sum(weight) over(order by turn) as total
        from
            Queue
    ) new_Queue
where
    total<=1000
order by 
    total desc
limit 1;

2.36 - 按分类统计薪水

-- 方法一
select
    'Low Salary' category,sum(income <20000) accounts_count 
from
    Accounts     
union
select
    'Average Salary' category,sum(income between 20000 and 50000) accounts_count 
from
    Accounts    
union
select
    'High Salary' category,sum(income>50000) accounts_count 
from
    Accounts ;

-- 方法二

select
    'Low Salary' category,count(*) accounts_count 
from
    Accounts 
where
    income <20000
union
select
    'Average Salary' category,count(*) accounts_count 
from
    Accounts 
where
    income between 20000 and 50000
union
select
    'High Salary' category,count(*) accounts_count 
from
    Accounts 
where
    income>50000;