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
分享到:
2015-01-09 09:42
浏览 2472
评论