这是测试数据的一次迭代:
DECLARE @trial TABLE (id int)
INSERT INTO @trial (id)
VALUES (0), (0),(3), (0), (0),(0), (7), (0),(9), (0), (0),(12), (0), (0),(15)
select * from @trial
数字将始终是0或行号的序列。在任何集合中,如果数字不为零,则该数字将始终代表行,因此它将始终增加。
我需要0来取下一个最大值。所以输出应该是这样的:
3
3
3
7
7
7
7
9
9
12
12
12
15
15
15
请帮忙!!
我想这就是您想要的
declare @trial table (id int)
insert into @trial (id)
values (0), (0),(3), (0), (0),(0), (7), (0),(9), (0), (0),(12), (0), (0),(15) ;
;with
seq_cte as (select row_number() over (order by (select null)) rn from @trial),
break_cte as (select s.rn, isnull(t.id, 0) input_n, iif(isnull(t.id, 0)=0, 0, 1) seq_break,
lag(iif(isnull(t.id, 0)=0, 0, 1), 1, 0) over (order by s.rn) seq_break_lag
from seq_cte s left join @trial t on s.rn=t.id),
group_cte as (select *, sum(bc.seq_break_lag) over (order by bc.rn) break_grp from break_cte bc),
join_v_cte as (select id, (row_number() over (order by id))-1 jrn from @trial where id<>0)
select vc.id
from group_cte gc
join
join_v_cte vc on gc.break_grp=vc.jrn;
结果
id
3
3
3
7
7
7
7
9
9
12
12
12
15
15
15
SQL表表示无序集合。没有排序,也没有累积最大值,除非您有一个指定排序的列。
这很容易通过具有identity
列来包括。这样的列将保留插入顺序,这显然是您想要的。所以,这就是你想要的:
DECLARE @trial TABLE (id int identity, val int)
INSERT INTO @trial (val)
VALUES (1), (0),(0), (0), (0),(0), (7), (0),(9), (0), (0),(12), (0), (14),(0)
select * from @trial
select max(val) over (order by id) as running_max
from @trial
order by id;
编辑:
在示例数据中,您希望查看向前和向后。为此,可以使用case
表达式来查找第一行具有0
:的情况
select (case when max(val) over (order by id) = 0
then min(case when val > 0 then val end) over (order by id desc)
else max(val) over (order by id)
end) as running_max
from trial
order by id;
这里有一个db<gt;不停摆弄