高频 sql 50题（基础5）

五、高级查询和连接

1.每位经理的下属员工数量

题目描述

 

题目思路：

题目要求是统计向经理汇报的员工人数，以及这些员工的平均年龄

1.将经理和其管理的下属连接在一张表上，并根据经理id进行分组

2.统计经理的员工人数和平均年龄

代码编写

select 
a.employee_id,
a.name,count(b.reports_to) as reports_count,
round(avg(b.age),0)as average_age
from Employees a join Employees b 
on b.reports_to=a.employee_id
group by a.employee_id
order by a.employee_id

2.员工的直属部门

题目描述

题目要求是查出员工所属的直属部门。

将员工分为两类分析，一类是只加入一个部门的，这类员工的直属部门就是他加入的唯一的部门；一类是加入多个部门的，这类员工的直属部门是枚举类型为 primary_flag="Y"的部门。

代码编写

elect
employee_id,
department_id 
from Employee 
where employee_id in
(select employee_id from Employee
group by employee_id
having count(department_id)=1)
or primary_flag="Y"

3.判断三角形

题目描述

 

代码编写

select 
x,
y,
z,
(case when x+y>z and x+z>y and y+z>x then "Yes"
else "No" end) as triangle 
from triangle

4.连续出现的数字

题目描述

 

代码编写

官方解法（代码来源leetcod官方）：

SELECT DISTINCT
    l1.Num AS ConsecutiveNums
FROM
    Logs l1,
    Logs l2,
    Logs l3
WHERE
    l1.Id = l2.Id - 1
    AND l2.Id = l3.Id - 1
    AND l1.Num = l2.Num
    AND l2.Num = l3.Num

官方解法直接用三表连接解决，简单粗暴，但不严谨，存在两个明显的漏电：

第一，当id并不连续，现实中常常会因为某些问题删除某行，似的id不连续，比如上一个id为5，下一个id为7；

第二，当一个数字连续出现三次以上，如5次，10次，n次；

在以上情况下，这种解法并不能完成这道题目的要求。

做法一（利用 lag ( )，lead ( )函数）：

select distinct num as ConsecutiveNums 
from (
    select id,num,
    lag(num) over (order by id) as num_per,
    lead(num) over (order by id) as num_next
    from Logs) a
where num=num_per and num=num_next
‘’

思路：在表格查找上下行都与该行的数值相等，并返回该数值的不重复结果 ，这种解法可以忽视id是否连续的问题，也不用考虑出现次数多于3次的问题，是个人感觉最简单快捷的方法。

Tip：

lag( ) ，查询当前行向上偏移n行后的结果。

lag（expression，offset，default_value），expression为待查询的列，offset为向上偏移的行数，default_value：为超出最上边界的默认值。

lead ( ) ,查询当前行向下偏移n行后的结果。

lead（expression，offset，default_value），expression为待查询的列，offset为向上偏移的行数，default_value：为超出最上边界的默认值。

做法二（row_number( ) over( ) ）：

select distinct num as ConsecutiveNums
from(
    select id,num,
    (row_number() over(order by id)-
     row_number() over(partition by num order by id)) as series
    from logs
) a
group by num,series
having count(series)>2

思路： 首先，用row_number( ) over( )对id进行重新排序，确保id是连续的；其次，用row_number( ) over()按照数字num分组并对id进行排序；再者，用两者的排序相减，如果数字连续出现，得到的差是相等的，最后根据num，series分组并统计series相同值的次数，筛选出大于或等于3次的情况。

5.指定日期的产品价格

题目描述

 

代码编写

做法一：

select p1.product_id,ifnull(new_price,10) as price
from(
     select distinct product_id from Products) as p1 
left join(
         select  product_id,new_price from Products 
         where (product_id,change_date) in(
             select product_id,max(change_date) from Products
             where change_date<="2019-08-16"
             group by product_id
         )
        )as p2
on p1.product_id=p2.product_id

思路：筛选出不重复的商品id作为表一，筛选商品id和每个商品在2019-08-16的日期之前的最新价格（如果商品在2019-08-16之后上架，则商品价格等于默认值）作为表二，将表一，二连接，并筛出商品id和售价，如果找不到商品的价格信息，商品价格默认为10。

做法二：

select distinct product_id, if(rnew_date is null, 10, new_price) as price
from (
  select *, rank() over(partition by product_id order by rnew_date desc) as series
  from (
    select *, if(change_date > '2019-08-16', null, change_date) as rnew_date
    from Products
  ) as a
) as b
where series = 1

思路： 每个商品在2019-08-16的日期之前的最新价格（如果商品在2019-08-16之后上架，则商品，更新日期默认为null），对商品id分组并根据更新日期降序排序，筛选日期序号为1（日期序号为1，说明这是该商品售价更新的最新日期）的商品id和售价的信息，如果商品的更新日期为null，商品价格默认为10。

6.最后一个进入巴士的人

题目描述

 

代码编写

select person_name from(
    select *,
    sum(weight) over(order by turn) as total from Queue
    ) as a
where total<=1000
order by turn desc
limit 1

思路：按照上车顺序 turn 将表格排序 ，计算出已上车的人的总质量，并限制总质量只能在1000以内，将符合要求的上车的人的顺序倒序排列，并选出第一条信息的乘客姓名。

7.按分类统计薪水

题目描述

 

代码编写

select 'Low Salary' as category,
sum(case when income<20000 then 1 else 0 end)as accounts_count 
from Accounts
union
select 'Average Salary' as category,
sum(case when income<=50000 and income>=20000 then 1 else 0 end)as accounts_count 
from Accounts
union
select 'High Salary' as category,
sum(case when income>50000 then 1 else 0 end)as accounts_count 
from Accounts

思路：依次返回每一类的查询结果，并用 UNION 函数 将这三个单独查询的结果组合在一起。