mysql - 如何在组mySQL中创建差异列

Question

我有一个来自以下结构的表:

   Id_no | year |
   --------------
   22222 | 1999 |
   22255 | 2000 |
   .....

我正在尝试为具有以下结构的表创建一个差异列，以便对每个 id 单独完成:

   Id_no | year | diff
   --------------------
   22222 | 1999 | 0
   22222 | 2000 | 1
   22222 | 2005 | 5
   55555 | 2000 | 0
   55555 | 2014 | 14

我尝试执行以下操作:

  select x.Id_no,
        x.year,
        IFNULL(x.year - max(y.year), 0) AS diff
  from my_table x
  left 
  join my_table y
  on y.year < x.year
  group by  
     x.Id_no, 
     x.year
  order by x.Id_no

结果不符合预期我怀疑差异的计算未按我的预期进行，例如:

     Id_no | year | diff
     ----------------------
     2898  | 1997 |  1   
     2898  | 1989 |  2

1997年和1989年的差距不是2

Answer 1

我建议使用相关子查询:

select t.*, coalesce(year - prev_year, 0) as diff
from (select t.*,
             (select max(t2.year)
              from my_table t2
              where t2.id_no = t.id_no and
                    t2.year < t.year
             ) prev_year
      from my_table t
     ) t;

这避免了对所有数据进行group by，并且可以利用my_table(id_no,year)上的索引。

此外，中间的子查询不是必需的。我只是将其包含在内，以便您可以看到前一年的情况和差异。