mysql - 使用 mySQL 按顺序检索值并减去行

Question

两天来我一直在解决一个问题，我需要帮助。

我想对 SQL 表中的连续行进行减法，但要先对值进行排序。

例如，我的 table 是这样的:

Name       | Activity  |  Day
John | Tennis | Day10
Bob | Swimming | Day9 
Mathilda | Tennis | Day2
John | Swimming | Day2
Bob | Tennis | Day2
John | Computer Science | Day 10

在这张表中，我想知道那些进行不止一项事件的人，两项事件之间的时间间隔是多少。

因此，在这个示例中，如果我们可以减去天数，我们会看到 John 和 Bob 有两项事件，并且 John 达到了他们的最大休息时间，时间为 8 天。

我尝试这样做:

SET @previousDay:=0;

SELECT Name, days, @previousDay - day, @previousDay:=day
FROM table
WHERE Name IN (SELECT Name FROM table group by Name having count(Name) > 1) 
ORDER BY Name, Day

但这完全是错误的，因为 ORDER BY 是在减法之后完成的。我没有看到好的结果，因为我想用排序的天数的连续值进行减法。

我似乎无法对 IN 结果进行排序，所以你知道是否有办法解决我的问题？

非常感谢您的帮助!!!

顺便说一句:我不知道如何正确格式化表格，因此任何帮助将不胜感激，非常感谢!

Answer 1

你可以这样做:

select t.*, (previousDay - day) as diff
from (select t.*,
             (@pd := (case when @n <> name
                           then if(@n := name, NULL, NULL) -- does happen
                           when (@tmp_pd := @pd) = null then null       -- never happens
                           when (@pd := day) = null then null   -- never happens
                           else @tmp_pd
                      end)
             ) as previousDay
      from t cross join
           (select @n := '', @pd = -1) params
      order by name, day
     ) t
where previousDay is not null;

请注意，这需要付出很大的努力才能将所有对变量的操作放在一个表达式中(因此在 case 条件中出现奇怪的 = NULL )。原因是 MySQL 不保证 select 子句中表达式的求值顺序，因此多个赋值可能会以错误的顺序求值。