mysql中，分析函数实现的方法

SELECT t2.team,
       MIN(t2.y) start_hm,
       MAX(t2.y) end_hm
  FROM (
        SELECT team,y,y - rank y1 from (
            SELECT tmp.team,
                   tmp.y,
                   IF(@groupid = tmp.team,@rank := @rank + 1,@rank := 1) AS rank,
                   @groupid := tmp.team
              FROM (SELECT team,y FROM teamtb ORDER BY y) tmp,
                   (SELECT @rank := 0,@groupid := '') a
            ) b
        ) t2
 GROUP BY t2.team,
          t2.y1
HAVING COUNT(*) > 1;


Lead OR LAG
select  t1.*,
		if(@prev_id = groupid, (t1.v - @prev_value), 0) as diff,
		@prev_value := t1.v,
		@prev_id := groupid
  from 	(
			select 1 groupid, 10 v union all
			select 1 groupid, 5 v union all
			select 1 groupid, 2 v union all
			select 2 groupid, 3 v union all
			select 2 groupid, 10 v
  		) t1,
  		(select @prev_value := null, @prev_id := null) var_init
 order by t1.groupid, t1.v