从多个值计算最大单间隙

Question

我的桌子：

id  name    year
1   Carl    1923
2   Carl    1924
3   Carl    1927
4   Carl    1939
5   Carl    1990
6   Carl    1992
7   Carl    1993
8   Suki    1962
9   Suki    1972
10  Suki    2002
11  Suki    2003

我想计算一下卡尔和苏基之间的最大间隔。所以我期待的结果是：

Carl    51
Suki    30

提前谢谢。

Answer 1

您确实需要一个lag()或lead()函数。否则，有一种方法是关联子查询：

select name, max(year - prev_year) as max_diff
from (select t.*,
             (select t2.year
              from t t2
              where t2.name = t.name and t2.year < t.year
              order by year desc
              limit 1
             ) as prev_year
      from t
     ) t
group by name;

如果您知道id没有空白(至少在名称内)，则可以使用更高性能的join查询：

select t.name, max(t.year - tprev.year) as max_diff
from t join
     t tprev
     on t.year = tprev.year + 1 and t.name = tprev.name
group by t.name;

Answer 2

对不起，我后来才意识到这不是PHP问题。也许你将来能帮到某人，不如你做以下几件事：

1. 通过SQL检索$query_result
2. 创建变量$name=“，$year=0，$diff=-1
3. 遍历每个$row并检查if $row['name'] is != $name if so then do $year=$row['year'] and $diff=0, else check if($diff<($row['year']-($year+$diff))) #update diff $diff=$row['year']-($year+$diff) $year=$row['year']
4. 对于打印，您可以在$row‘’name‘是!= $name时打印以前的值，或者以{key:value} JSON格式保存它们并在最后打印！