高频 SQL 50 题（基础版）- 05. 高级查询和连接

sjh1
234 阅读12分钟

来源：高频 SQL 50 题（基础版）leetcode.cn/studyplan/s…

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

对于此问题，我们将至少有一个其他员工需要向他汇报的员工，视为一个经理。

编写SQL查询需要听取汇报的所有经理的ID、名称、直接向该经理汇报的员工人数，以及这些员工的平均年龄，其中该平均年龄需要四舍五入到最接近的整数。

返回的结果集需要按照 employee_id 进行排序。

查询结果的格式如下：

思路 + SQL

  1. 先做内连接，将员工和上司的信息关联起来：
select
  *
from
  Employees e1
  inner join Employees e2 on e1.reports_to = e2.employee_id;

  1. 再聚类做相应的分组后计算即可得到答案：
select
  e2.employee_id,
  e2.name,
  count(e1.employee_id) as reports_count,
  round(avg(e1.age)) as average_age
from
  Employees e1
  inner join Employees e2 on e1.reports_to = e2.employee_id
group by
  e2.employee_id
order by
  e2.employee_id;

1789. 员工的直属部门

一个员工可以属于多个部门。当一个员工加入超过一个部门的时候，他需要决定哪个部门是他的直属部门。请注意，当员工只加入一个部门的时候，那这个部门将默认为他的直属部门，虽然表记录的值为'N'.

请编写解决方案，查出员工所属的直属部门。

返回结果 没有顺序要求 。

返回结果格式如下例子所示：

思路 + SQL 1 （UNION）

这题比较送分，重点是需要想到用 union 来关联不同的条件。这里不能用 union all，否则会有重复的情况。

-- 有选择了直属部门的员工
select
  employee_id,
  department_id
from
  Employee
where
  primary_flag = 'Y'
union
-- 只有一个部门的员工
select
  employee_id,
  department_id
from
  Employee
group by
  employee_id
having
  count(department_id) = 1;

思路 + SQL 2（窗口函数）

这里可以想到，先用窗口函数生成每个员工的访问部门的数量后，再进行 where 处理即可。

  1. 先生成拓展后的表：
select
  *
from
  (
    select
      *,
      count(*) over (partition by employee_id) count
    from
      Employee
  ) as tmp;
  1. 然后再通过 where 进行筛选即可。条件：参与部门只有一个，或者有直属部门标记的部门
select
  employee_id,
  department_id
from
  (
    select
      *,
      count(*) over (partition by employee_id) count
    from
      Employee
  ) as tmp
where
  count = 1
  or primary_flag = 'Y';

610. 判断三角形

对每三个线段报告它们是否可以形成一个三角形。

以 任意顺序 返回结果表。

查询结果格式如下所示。

思路 + SQL 1

很快就能想起来的办法，通过 if 判断是否符合三角形的条件，然后输出结果。

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

思路 + SQL 2

如果能用 if，那肯定是能用 case when 来解决的：

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;

180. 连续出现的数字

找出所有至少连续出现三次的数字。

返回的结果表中的数据可以按 任意顺序 排列。

结果格式如下面的例子所示：

思路 + SQL 1

土方法，大道至简，直接三表联查：

select
  distinct l1.num as ConsecutiveNums
from
  Logs l1,
  Logs l2,
  Logs l3
where
  l1.id + 1 = l2.id
  and l2.id + 1 = l3.id
  and l1.num = l2.num
  and l2.num = l3.num;

写成内连接的形式也是可以的。

思路 + SQL 2

上面的方案是有缺陷在的。

首先，有个前提是 id 是必须连续的，如果 id 不连续，那么不可能通过 id + 1 这种简单判断方法来判断该数字是否连续出现。

其次，如果题目要求需要 4 题还是 5 题呢，这样搞又太呆了，如果能一劳永逸的方法解决类似的问题，这肯定是更好的。真正的解法如下：

  1. 定义两个变量，@count用于统计重复次数，@prev用于存储前一个数。
SELECT
  @count := 0,
  @prev := NULL
) AS vars
  1. 每行 select 的时候，当前的数和之前存的数进行比较，如果是相同的数，@count加一。
SELECT
  *,
  -- 上一个数的值
  @prev,
  -- 如果和上一个连续数相等，count + 1，否则重置为 0
  @count := IF(@prev = num, @count + 1, 1) AS count,
  -- 当前的数存储到 @prev 中
  @prev := num
FROM
  Logs,
  (
    SELECT
      @count := 0,
      @prev := NULL
  ) AS vars
ORDER BY
  id;

可以看到，每行统计了重复的次数了，这样就可以进行下一步的 select 了。

  1. select 语句挑出重复次数大于 3 的，就可以得到结果。
SELECT
  num AS ConsecutiveNums
FROM
  (
    SELECT
      *,
      -- 如果和上一个连续数相等，count + 1，否则重置为 0
      @count := IF(@prev = num, @count + 1, 1) AS count,
      -- 当前的数存储到 @prev 中
      @prev := num
    FROM
      Logs,
      (
        SELECT
          @count := 0,
          @prev := NULL
      ) AS vars
    ORDER BY
      id
  ) AS tmp
WHERE
  count >= 3
GROUP BY
  num;

思路 + SQL 3 （窗口函数）

连续出现的数字，在相同数字出现的序号和所有数字出现的次数的编号的差值是一样的。

  1. 先对 num 进行分组，并使用 row_number() 来统计出现的序号。

序号相关的窗口函数如下：

  • rank()：跳跃排序，可理解为运动会场景下成绩的排名。相同数据排名相同，比如并列第 1，则两行数据都标为 1，下一位将是第 3 名，中间的第 2 名被直接跳过了；
  • dense_rank()：是连续排序的，没有排名跳跃的情况。比如两条并列第 1，则两行数据都标为 1，下一个排名将是第 2 名。类似于游戏攻略里的角色强度梯度排名；
  • row_number()：简单的出现顺序排序。先查出的排名在前，后查出的在后，没有重复值。

下图的例子就会更具象，不同的排序函数查到的结果差别是很大的：

来源：《通俗易懂的学会：SQL窗口函数》 www.zhihu.com/tardis/zm/a…

  1. 根据原本的 id 创造连续的编号

同样的，num 分组后，在组内用 row_number() 函数进行顺序的编号，即可得到编号连续数字的出现顺序编号。

select
  *,
  row_number() over (
    partition by num
    order by
      id
  ) as rn
from
  Logs;

同时，因为要考虑到连续的数字，id 并不一定是连续的情况（有可能中间的数被删过了），所以需要再补充一个，按照 id 出现的先后顺序，这个新编号就不可能不连续了，这里命名为 id2：

select
  *,
  row_number() over (
    partition by num
    order by
      id
  ) as number_count,
  row_number() over (
    order by
      id
  ) as id2
from
  Logs;

接下来可以想一下，在连续数字出现的情况下，连续数字出现编号 number_count 和出现编号 id2 的差值是固定值，因为递增的情况下，这两个值的下一个数都会同时加 1。在后续的连续 n 个数的情况下，（number_count + n）和 （id2 + n）的差值仍然是 number_count 和 id2 的差值。

所以用差值和 num 对表格分组。如果分组完发现 count 计数大于等于 3，那就说明了是连续出现的数字。

select
  -- 注意去重
  distinct num as ConsecutiveNums
from
  (
    select
      *,
      -- 相同数字的出现顺序编号
      row_number() over (
        partition by num
        order by
          id
      ) as number_count,
      -- 依据 id 得到的出现顺序编号
      row_number() over (
        order by
          id
      ) as id2
    from
      Logs
  ) as t
group by
  t.num,
  (t.id2 - t.number_count)
having
  count(t.num) >= 3;

1164. 指定日期的产品价格

编写一个解决方案，找出在 2019-08-16 时全部产品的价格，假设所有产品在修改前的价格都是 10 。

以 任意顺序 返回结果表。

结果格式如下例所示。

思路 + SQL

  1. 查出 2019-08-16 前最后一次的价格更新日期：
select
  product_id,
  max(change_date)
from
  Products
where
  change_date <= '2019-08-16'
group by
  product_id;
  1. 用上面搜索出的结果去查表，返回价格。
select
  product_id,
  new_price as price
from
  Products
where
  (product_id, change_date) in (
    select
      product_id,
      max(change_date) as change_date
    from
      Products
    where
      change_date <= '2019-08-16'
    group by
      product_id
  );

有些商品的价格中间没有改过价格，这些价格初始是 10 元，这些需要囊括进来。

  1. 将一个包括所有用户但是 id 不重复的表和上面的结果表做左连接：
select
  *
from
	-- distinct id 列表
  (
    select
      distinct product_id
    from
      Products
  ) p1
  left join (
    -- 2019-08-16 及以前最后一次价格修改表
    select
      product_id,
      new_price
    from
      Products
    where
      (product_id, change_date) in (
        select
          product_id,
          max(change_date) as change_date
        from
          Products
        where
          change_date <= '2019-08-16'
        group by
          product_id
      )
  ) p2 on p1.product_id = p2.product_id;
  1. 使用 ifNull 函数，new_price 为 null 的设置为 10 元即可。
select
  p1.product_id,
  -- 如果为空，说明没改过价格，起始价格为 10
  ifNull(p2.new_price, 10) as price
from
	-- distinct id 列表
  (
    select
      distinct product_id
    from
      Products
  ) p1
  left join (
    -- 2019-08-16 及以前最后一次价格修改表
    select
      product_id,
      new_price
    from
      Products
    where
      (product_id, change_date) in (
        select
          product_id,
          max(change_date) as change_date
        from
          Products
        where
          change_date <= '2019-08-16'
        group by
          product_id
      )
  ) p2 on p1.product_id = p2.product_id;

1204. 最后一个能进入巴士的人

有一队乘客在等着上巴士。然而，巴士有 1000 千克 的重量限制，所以其中一部分乘客可能无法上巴士。

编写解决方案找出 最后一个 上巴士且不超过重量限制的乘客，并报告 person_name 。题目测试用例确保顺位第一的人可以上巴士且不会超重。

返回结果格式如下所示。

思路 + SQL 1 （自连接 + 笛卡尔积）

  1. 先让 Queue 自己跟自己做笛卡尔积，并附加条件为 q1.turn >= q2.turn，目的在于搜索出每个特定的乘客作为最后上车人时，自身以及之前乘客的信息。
select
  *
from
  Queue q1
  cross join Queue q2 on q1.turn >= q2.turn
order by
  q1.turn;

搜索结果：

  1. 得到上面的结果后，就可以分组了，根据每个用户 id，可以计算出当该 id 作为最后一个人上车时，这辆车上人数的总重量。
select
  q1.person_id,
  q1.person_name,
  q1.turn,
  sum(q2.weight)
from
  Queue q1
  cross join Queue q2 on q1.turn >= q2.turn
group by
  q1.person_id
order by 
  q1.turn desc;
  1. 最后找出不超过 1000kg 的人即可。
select
  q1.person_name
from
  Queue q1
  cross join Queue q2 on q1.turn >= q2.turn
group by
  q1.person_id
having
  sum(q2.weight) <= 1000
order by
  q1.turn desc
limit
  1;

思路 + SQL 2 （变量计算）

上面因为需要自连接加上笛卡尔积，比自己想的麻烦太多了，性能也不会好到哪里去。最优的方法应该是不用做任何复杂处理，通过上车轮次去累计总重量，如果超重的话返回上一个用户即可。

  1. 将累计重量附加到表上：
select
  *,
  @total_weight := @total_weight + weight as totalWeight
from
  Queue,
  (
    select
      @total_weight := 0
  ) as var
order by
  turn;
  1. 从这个结果中筛选出刚好没超重的最晚上车用户即可
select
  a.person_name
from
  (
    select
      person_name,
      @total_weight := @total_weight + weight as totalWeight
    from
      Queue,
      (
        select
          @total_weight := 0
      ) as var
    order by
      turn
  ) a
where
  a.totalWeight <= 1000
order by
  a.totalWeight desc
limit
  1;

思路 + SQL 3 （窗口函数）

这个场景是很适合窗口函数进行处理的，先计算重量，后进行条件判断，即可获得结果，易读且高效。

  1. 先用窗口函数，通过 turn 排序，并用 sum 聚合函数进行累计计算每个上车轮次下，乘客的总重量：
select
  *,
  sum(weight) over(
    order by
      turn
  ) as totalWeight
from
  Queue
  1. 上面的查询结果作为临时表，对表进行条件判断、排序等操作即可获取到结果。
select
  person_name
from
  -- 拓展了每个轮次车上累计重量的乘客信息表
  (
    select
      *,
      sum(weight) over(
        order by
          turn
      ) as totalWeight
    from
      Queue
  ) as t
where
  totalWeight <= 1000
order by
  totalWeight desc
limit
  1;

1907. 按分类统计薪水

查询每个工资类别的银行账户数量。 工资类别如下：

  • "Low Salary"：所有工资 严格低于 20000 美元。
  • "Average Salary"： 包含 范围内的所有工资 [$20000, $50000] 。
  • "High Salary"：所有工资 严格大于 50000 美元。

结果表 必须 包含所有三个类别。 如果某个类别中没有帐户，则报告 0 。

按 任意顺序 返回结果表。

查询结果格式如下示例。

思路 + SQL 1（UNION 所有查询）

力大砖飞法，实用：

-- 低薪人数统计
select
  "Low Salary" as category,
  count(account_id) as accounts_count
from
  Accounts
where
  income < 20000
union
-- 中等薪水人数统计
select
  "Average Salary" as category,
  count(account_id) as accounts_count
from
  Accounts
where
  income >= 20000
  and income <= 50000
union
-- 高薪人数统计
select
  "High Salary" as category,
  count(account_id) as accounts_count
from
  Accounts
where
  income > 50000;

思路 + SQL 2（SELECT 里做判断）

稍微做些小小优化，可以快一点，但不多：

-- 低薪人数统计
select
  "Low Salary" as category,
  count(
    income < 20000
    or null
  ) as accounts_count
from
  Accounts
union
-- 中等薪水人数统计
select
  "Average Salary" as category,
  count(
    (
      income between 20000
      and 50000
    )
    or null
  ) as accounts_count
from
  Accounts
union
-- 高薪人数统计
select
  "High Salary" as category,
  count(
    income > 50000
    or null
  ) as accounts_count
from
  Accounts;

思路 + SQL 3（用 SUM 代替 COUNT）

使用 sum，写法可以更简约点。sum 将 true 值转化为 1，false 转化为 0，就可以去除方法 2 的 or null 操作了。（count 函数则会对所有的非空值都进行计数。）

-- 低薪人数统计
select
  "Low Salary" as category,
  sum(income < 20000) as accounts_count
from
  Accounts
union
-- 中等薪水人数统计
select
  "Average Salary" as category,
  sum(
    income between 20000
    and 50000
  ) as accounts_count
from
  Accounts
union
-- 高薪人数统计
select
  "High Salary" as category,
  sum(income > 50000) as accounts_count
from
  Accounts;
avatar
文章
阅读
粉丝