mysql - 需要帮助使用局部变量减去具有多个组的前一行值

Question

我有如下 3 个表:

+-----------+--------------+------+-----+---------+-------+
| Field     | Type         | Null | Key | Default | Extra |
+-----------+--------------+------+-----+---------+-------+
| id        | int(11)      | NO   | PRI | 0       |       |
| full_name | varchar(200) | YES  |     | NULL    |       |
| gender    | varchar(1)   | YES  |     | NULL    |       |
+-----------+--------------+------+-----+---------+-------+
+----------+---------+------+-----+---------+-------+
| Field    | Type    | Null | Key | Default | Extra |
+----------+---------+------+-----+---------+-------+
| actor_id | int(11) | YES  | MUL | NULL    |       |
| movie_id | int(11) | YES  | MUL | NULL    |       |
| salary   | int(11) | YES  |     | NULL    |       |
+----------+---------+------+-----+---------+-------+
+-------+--------------+------+-----+---------+-------+
| Field | Type         | Null | Key | Default | Extra |
+-------+--------------+------+-----+---------+-------+
| id    | int(11)      | NO   | PRI | 0       |       |
| title | varchar(100) | YES  |     | NULL    |       |
| year  | int(11)      | YES  |     | NULL    |       |
| genre | varchar(100) | YES  |     | NULL    |       |
+-------+--------------+------+-----+---------+-------+

我正在尝试找出哪位 Actor 的电影间隔时间最长。

在参与者相同的情况下(否则默认为 0)，我尝试使用局部变量来引用前一行值。但是由于某种原因，@previousName 变量返回随机名称列表。

这是代码:

SELECT 
    a.full_name, 
    m.year,
    m.title,
    @PreviousName,
    @PreviousYear,
    if(@PreviousName = a.full_name, m.year - @PreviousYear, 0) AS Delta,
    @PreviousName := a.full_name,
    @PreviousYear := m.year 
FROM
    actors AS a
        INNER JOIN
    cast AS c ON a.id = c.actor_id
        INNER JOIN
    movies AS m ON c.movie_id = m.id,
    (SELECT @PreviousName := null, @PreviousYear := 999) as SQLVars
ORDER BY full_name;

这是我得到的: Picture of result table

注意，我使用的是 mySQL V5.7，因此窗口函数不是一个选项。

Answer 1

您可以使用相关子查询来做到这一点:

select mc.*, (year - prev_year) as diff
from (select c.*, m.year,
             (select m2.year
              from movies m2 join
                   cast c2
                   on c2.movie_id = m2.id
              where c2.actor_id = c.actor_id and
                    m2.year < m.year
              order by m2.year desc
              limit 1
             ) prev_year
      from movies m join
           cast c
           on c.movie_id = m.id
     ) mc
order by diff desc;

这假设一个 Actor 不在同一年出演两部电影。如果您有发布日期或其他信息，那么排序时间会更有效。