高频 SQL 50 题（基础版）- 02. 链接

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

1378. 使用唯一标识码替换员工ID

展示每位用户的 唯一标识码（unique ID ）；如果某位员工没有唯一标识码，使用 null 填充即可。

你可以以 任意 顺序返回结果表。

返回结果的格式如下例所示。

思路

这里有个要求：如果某位员工没有唯一标识码，使用 null 填充即可。

这就需要使用 LEFT JOIN 来链接表，让 Employees 里没有被 EmployeeUNI 匹配到的行也能包含在结果中。

SQL

select
  EmployeeUNI.unique_id,
  Employees.name
from
  Employees
  left join EmployeeUNI on Employees.id = EmployeeUNI.id;

1068. 产品销售分析 I

编写解决方案，以获取 Sales 表中所有 sale_id 对应的 product_name 以及该产品的所有 year 和 price 。

返回结果表 无顺序要求 。

结果格式示例如下。

SQL 1

直接查询：

select
  p.product_name,
  s.year,
  s.price
from
  Sales s,
  Product p
where
  s.product_id = p.product_id;

SQL 2

内连接。使用内连接会更有表关联的意思，可读性比直接查询好。

select
  p.product_name,
  s.year,
  s.price
from
  Sales s
  inner join Product p on s.product_id = p.product_id;

1581. 进店却未进行过交易的顾客

有一些顾客可能光顾了购物中心但没有进行交易。请你编写一个解决方案，来查找这些顾客的 ID ，以及他们只光顾不交易的次数。

返回以 任何顺序 排序的结果表。

返回结果格式如下例所示。

示例 1：

思路

因为有些人只有拜访但是没有购物，这样的话在 Transactions 表上是不存在的，所以需要 LEFT JOIN 进行表连接。

连接后将没有购物过的（transaction_id 为 NULL）的查出来后，用 group by 结合 count 函数进行分组计数，即可得到答案。

SQL

select
  v.customer_id,
  count(v.customer_id) as count_no_trans
from
  Visits v
  left join Transactions t on v.visit_id = t.visit_id
where
  t.transaction_id is null
group by
  customer_id;

197. 上升的温度

编写解决方案，找出与之前（昨天的）日期相比温度更高的所有日期的 id 。

返回结果 无顺序要求 。

结果格式如下例子所示。

思路

这题要求是和前一天比，所以需要自连接了，表去连接昨天的表，连接条件是今天比昨天多一天。

所以这题的难度在于怎么算多一天。有一个日期的相关函数 DateDiff。

DATEDIFF(enddate, startdate)，enddate 和 startdate 代表结束日期和开始日期（被减数和减数）。

SQL

select
  w.id
from
  Weather w
  inner join Weather wy on dateDiff(w.recordDate, wy.recordDate) = 1
where
  w.temperature > wy.temperature;

1661. 每台机器的进程平均运行时间

现在有一个工厂网站由几台机器运行，每台机器上运行着 相同数量的进程 。编写解决方案，计算每台机器各自完成一个进程任务的平均耗时。

完成一个进程任务的时间指进程的 'end' 时间戳 减去 'start' 时间戳。平均耗时通过计算每台机器上所有进程任务的总耗费时间除以机器上的总进程数量获得。

结果表必须包含machine_id（机器ID） 和对应的 average time（平均耗时） 别名 processing_time，且四舍五入保留3位小数。

以 任意顺序 返回表。

具体参考例子如下。

思路 1

1. 表先内连接，组成一个查询结果。连接的条件比较复杂，前边取开始时间，后边取结束时间，然后再通过自身的 process_id 拼接起来。结果行中包含起始、结束时间：

select
  *
from
  Activity as a1
  inner join Activity as a2 on a1.machine_id = a2.machine_id
  and a1.process_id = a2.process_id
  and a1.activity_type = 'start'
  and a2.activity_type = 'end'

查询结果如下：

2. 计算差值后对机型进行 group by，然后每组取平均值即可：

select
  a1.machine_id,
  round(avg(a2.timestamp - a1.timestamp), 3) as processing_time
from
  Activity as a1
  inner join Activity as a2 on a1.machine_id = a2.machine_id
  and a1.process_id = a2.process_id
  and a1.activity_type = 'start'
  and a2.activity_type = 'end'
group by
  machine_id;

round 函数用于四舍五入，用法如下：

ROUND(number, decimals)

参数解释如下：

• number：要进行四舍五入的数字。
• decimals：保留的小数位数。如果省略此参数，则默认为 0。

SQL 1

select
  a1.machine_id,
  round(avg(a2.timestamp - a1.timestamp), 3) as processing_time
from
  Activity as a1
  inner join Activity as a2 on a1.machine_id = a2.machine_id
  and a1.process_id = a2.process_id
  and a1.activity_type = 'start'
  and a2.activity_type = 'end'
group by
  machine_id;

思路 2

看到解答里有老哥直接通过三元判断符的形式，不用连接表，节省了大量的时间。

三元判断的方式是，判断该字段是初始的 timestamp 还是结束的，如果是初始的，那么加个负号，那么分组后的总和就是结束和初始的总差值，当然平均值也可以算出来了。但是 avg 的使用是 sum 除以行数，即我计算的总差值是 3 组，但是除的时候除了 6，显然是不对的，所以需要总差值乘以 2 抵消。

三元运算符 if 的使用方法为：

IF(condition, true_value, false_value)

参数解释如下：

• condition：条件表达式，可以是任何返回布尔值的表达式。
• true_value：如果条件为真时的返回值。
• false_value：如果条件为假时的返回值。

SQL 2

select
  machine_id,
  round(
    avg(
      if(activity_type = 'start', - timestamp, timestamp) * 2
    ),
    3
  ) as processing_time
from
  Activity
group by
  machine_id;

577. 员工奖金

编写解决方案，报告每个奖金 少于 1000 的员工的姓名和奖金数额。

以 任意顺序 返回结果表。

结果格式如下所示。

思路

没啥难点，有些员工没有奖金，所以需要左连接；同时奖金需要额外做空值的判断。

SQL

select
  e.name,
  b.bonus
from
  Employee as e
  left join Bonus b on e.empId = b.empId
where
  bonus < 1000
  or bonus is null;

1280. 学生们参加各科测试的次数

查询出每个学生参加每一门科目测试的次数，结果按 student_id 和 subject_name 排序。

查询结构格式如下所示。

示例 1：

思路

1. 需要查询出每个学生参加每一门科目测试的次数，所以这边需要学生表和课程表做一次笛卡尔积：

select
  *
from
  Students stu
  cross join Subjects sub
order by
  stu.student_id,
  sub.subject_name;

这样就搞出了所有学生对应所有科目的表，只剩下没统计次数。

2. 这个笛卡尔积表和考试表做一次左连接（因为有些学生没参加某些科目的考试），链接规则为考试名相同且学生号也相同

select
  *
from
  (
    Students stu
    cross join Subjects sub
  )
  left join Examinations e on sub.subject_name = e.subject_name
  and stu.student_id = e.student_id
order by
  stu.student_id,
  sub.subject_name;

这样就查出来了所有学生的考试记录，没考试过的也会记录在里边（没考试过的右侧 student_id 和 subject_name 会被标记为 NULL）

3. group by 组合 count 进行计数，就完事了。需要注意的是，分组要学生名和课程名称一块分组。

SQL

select
  stu.student_id,
  stu.student_name,
  sub.subject_name,
  -- 统计有考试记录的行
  count(e.subject_name) as attended_exams
from
  -- 学生和学科做笛卡尔积
  (
    Students stu
    cross join Subjects sub
  )
  -- 笛卡尔积表和考试做左连接
  left join Examinations e on sub.subject_name = e.subject_name
  and stu.student_id = e.student_id
group by
  stu.student_name,
  sub.subject_name
order by
  stu.student_id,
  sub.subject_name;

570. 至少有5名直接下属的经理

查询至少有5名直接下属的经理 。

以 任意顺序 返回结果表。

查询结果格式如下所示。

思路 + SQL 1

1. 先搜索出有五名及以上的下属的 managerId，需注意的是，having 为分组后的筛选条件，where 为分组前的筛选，需要分清楚差别。

select
  managerId
from
  Employee
group by
  managerId
having
  count(managerId) >= 5;

2. 然后根据这个 id 去表里查人就行了

select
  name
from
  Employee
where
  id in (
    select
      managerId
    from
      Employee
    group by
      managerId
    having
      count(managerId) >= 5
  );

思路 + SQL 2

1. 还是先查出有五名及以上的下属的 managerId

select
  managerId
from
  Employee
group by
  managerId
having
  count(managerId) >= 5;

2. 将 Employee 表和五名以上下属的 managerId 做内连接，即可得到结果

select
  name
from
  employee as e
  inner join (
    select
      managerId
    from
      Employee
    group by
      managerId
    having
      count(managerId) >= 5
  ) as m on e.id = m.managerId;

1934. 确认率

用户的 确认率 是 'confirmed' 消息的数量除以请求的确认消息的总数。没有请求任何确认消息的用户的确认率为 0 。确认率四舍五入到 小数点后两位 。

编写一个SQL查询来查找每个用户的 确认率 。

以 任意顺序 返回结果表。

查询结果格式如下所示。

思路 + SQL 1

1. 因为有些用户注册并没有去用，所以需要做左连接，将用户表和确认表关联起来：

select
  *
from
  Signups as s
  left join Confirmations as c on s.user_id = c.user_id;

2. 利用上面老哥那边学过的三元表达式，用来统计平均值。如果 action 为 confirmed 就为 1，否则其他情况为 0。用 avg 和 round 包裹，即可获取确认率。

select
  s.user_id,
  round(avg(if(c.action = 'confirmed', 1, 0)), 2) as confirmation_rate
from
  Signups as s
  left join Confirmations as c on s.user_id = c.user_id
group by
  s.user_id;

思路 + SQL 2

看到评论里有其他老哥使用 case 表达式来进行更直白的计算。在 action = 'confirmed' 的情况下才进行计数。

case 函数的用法和 if 十分的接近，只不过 if 需要返回真值和假值，case 会更加的灵活。

SELECT column_name,
       CASE status
           WHEN 1 THEN 'Active'
           WHEN 2 THEN 'Inactive'
           ELSE 'Unknown'
       END AS status_text
FROM your_table;

下面是在 action = 'confirmed' 情况下标记为 1 其他情况标记为零的写法：

SELECT
  SUM(
    CASE
      WHEN action = 'confirmed' THEN 1
    ELSE 0
    END
  ) AS count
FROM
  your_table;

所以可以直接在 round 函数里手动计算平均值，计算方法为，action = 'confirmed' 的行除以总行数：

select
  s.user_id,
  round(
    count(
      case
        when c.action = 'confirmed' then s.user_id
      end
    ) / count(s.user_id),
    2
  ) as confirmation_rate
from
  Signups s
  left join Confirmations c on s.user_id = c.user_id
group by
  s.user_id;