7,388
社区成员
发帖
与我相关
我的任务
分享
with tt as
(select a.name,
a.seq,
a.money,
case--若存在上连续seq,则得到上连续seq
when exists (select 1
from etltable b
where b.name = a.name
and b.seq = a.seq - 1) then
a.seq - 1
else--否则上连续seq为本身
a.seq
end as mark1,
case--若存在下连续seq,则得到下连续seq
when exists (select 1
from etltable b
where b.name = a.name
and b.seq = a.seq + 1) then
a.seq + 1
else--否则下连续seq为本身
a.seq
end as mark2
from etltable a)
select dd.name,
dd.minseq,
dd.maxseq,(select sum(c.money)
from etltable c
where c.name = dd.name
and c.seq between dd.minseq and dd.maxseq)
from(select name,
mark1,
min(mark1) as minseq,
max(mark2) as maxseq
from (select a.* --获取连续数据,且可获取连续数据中最小上连续seq和最大上连续seq
from tt a, tt b
where a.name = b.name
and a.seq <> b.seq
and a.mark1 = b.mark1
union all
select a.* --获取孤立数据,最小上连续seq和最大上连续seq此时均为seq
from tt a
where mark1 = mark2)
group by name, mark1
order by name, min(seq)
) dd
insert into etltarget
select name,min(seq),max(seq),sum(money) from etltable group by name