mysql - 如何在 SQL 中查找一列上的元素以查找其他列中间隙不超过 3 个的值

Question

我有一个名为 emp_table 的 sql View ，如下所示:

+----------+----------+------+
| actor_id | movie_id | year |
+----------+----------+------+
|        2 |   280088 | 2002 |
|        2 |   396232 | 2000 |
|        3 |   376687 | 2000 |
|        4 |   336265 | 2001 |
|        5 |   135644 | 1953 |
|        6 |    12083 | 1996 |
|        7 |   252053 | 1993 |
|        7 |   402635 | 1992 |
|        7 |   409592 | 1995 |
|        8 |   101866 | 2000 |
|        9 |   336265 | 2001 |
|       10 |    12148 | 2000 |
|       11 |    80189 | 2001 |
|       12 |    12148 | 2000 |
|       13 |    80189 | 2001 |
|       14 |    70079 | 1982 |
|       15 |    12148 | 2000 |
|       16 |   242675 | 1991 |
|       17 |   105231 | 1993 |
|       17 |   242453 | 1988 |
+----------+----------+------+

...等等。我需要找到所有职业生涯间隔从未超过 3 年的 actor_id。这意味着我需要计算所有 Actor ，如果我计算他们在电影中表演的唯一年份数，然后对其进行排序，那么年份之间的最大连续差异永远不会超过 3 年。请帮我解决这个sql查询。我尝试过 sql self join 但无法更多地考虑它。

所有 SQL 代码仅适用于 MySQL。

注意您可以认为 actor_id 和 movie_id 只有一种组合。

预期结果

+----------+----------+
| actor_id | max_gap  |
+----------+----------+
|        2 |   2      |
|        3 |   0      |
|        4 |   0      |
|        5 |   0      |
|        6 |   0      |
|        7 |   2      |
|        . |   .      |
|        . |   .      |
|        . |   .      |
|       17 |   5      |
+----------+----------+

等等

注释 2: 对于输出中的这么多更改，我们深表歉意。这是最终版本，此后不再进行任何更改。

Answer 1

在 MySQL 8 和 MariaDB 10.2 中，您可以使用窗口函数 LEAD()获取 Actor 的下一个连续演出年份(或 LAG() 获取最后一个 Actor )。然后你只需要在外部查询中获取最大差异即可。

with tmp as (
  select 
      actor_id,
      year,
      lead(year) over (partition by actor_id order by year) as year_lead
  from emp_table e
)
select actor_id, coalesce(max(year_lead - year), 0) as max_gap
from tmp
group by actor_id
having max_gap <= 3;

演示:https://www.db-fiddle.com/f/cWChT2TqLuRT8bW1zcM9G2/0