mysql中的分析函数_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

分享到：

2015-01-09 09:42

浏览 2472

评论