脚本内容
/*原始数据*/
drop table test_tab purge;
create table test_tab as
(
select 1 as seqno,50 as amount from dual
union all
select 2 as seqno,-100 as amount from dual
union all
select 3 as seqno,20 as amount from dual
union all
select 4 as seqno,50 as amount from dual
union all
select 5 as seqno,-20 as amount from dual
union all
select 6 as seqno,-30 as amount from dual
union all
select 7 as seqno,40 as amount from dual
union all
select 8 as seqno,100 as amount from dual
union all
select 9 as seqno,20 as amount from dual
union all
select 10 as seqno,-70 as amount from dual
union all
select 11 as seqno,40 as amount from dual
);
复制要求
按序号seqno累计
上月累计金额＝amount＋上月累计金额
但是如果上月累计金额是正值，就不计入下个月，重新累计，结果如下：
SEQNO AMOUNT TOT_AMOUNT 1 50 50 2 -100 -100 3 20 -80 4 50 -30 5 -20 -50 6 -30 -80 7 40 -40 8 100 60 9 20 20 10 -70 -70 11 40 -30复制
评论
广州_老虎刘
8月前
评论
0--oracle 12c+ 的写法
with test_tab(seqno,amount) as
(
select 1 as seqno,50 as amount from dual union all
select 2 as seqno,-100 as amount from dual union all
select 3 as seqno,20 as amount from dual union all
select 4 as seqno,50 as amount from dual union all
select 5 as seqno,-20 as amount from dual union all
select 6 as seqno,-30 as amount from dual union all
select 7 as seqno,40 as amount from dual union all
select 8 as seqno,100 as amount from dual union all
select 9 as seqno,20 as amount from dual union all
select 10 as seqno,-70 as amount from dual union all
select 11 as seqno,40 as amount from dual union all
select 12 as seqno,40 as amount from dual union all
select 13 as seqno,-40 as amount from dual union all
select 14 as seqno,40 as amount from dual
)--select * from test_tab
select seqno,amount,tot_amount
from test_tab
match_recognize
(
order by seqno
measures
sum(amount) as tot_amount
all rows per match
pattern( a* b)
define
a as sum(amount)<0
,b as amount>0 and sum(amount)>0
);
8月前
点赞 评论
Thomas
2年前
评论
0
其实用递归写法，很多脚本都是雷同的
with t(seqno,amount,tot_amount) AS
(select seqno,amount,amount as tot_amount from test_tab where seqno=1
union all
select s.seqno,s.amount,case when t.tot_amount<0 then t.tot_amount+s.amount else s.amount end from t,test_tab s where t.seqno+1=s.seqno)
select * from t;
2年前
点赞 评论
阳菜
2年前
评论
0/*临时变量累加*/
create table test1(seqno integer, amount integer);
insert into test1(seqno,amount) values (1,50),(2,-100),(3,20),(4,50 ),(5,-20 ),(6,-30),(7,4 ),(8,100),(9,20 ),(10,-70),(11,40 );
SELECT
seqno,
amount,
(case when @t_amount>0 then@t_amount else @t_amount := @t_amount + amount end )
AS t_amount
FROM
test1,(select @t_amount:=0) t;
2年前
点赞 1
阳菜
2年前
回复
0/*with recursive 其中判断改if语句（误）*/
create table test1(seqno integer, amount integer);
insert into test1(seqno,amount) values (1,50),(2,-100),(3,20),(4,50 ),(5,-20 ),(6,-30),(7,4 ),(8,100),
(9,20 ),(10,-70),(11,40 );
with recursive test2 (seqno,amount,t_amount) as(
select a.seqno,a.amount,a.amount t_amount from test1 a where a.seqno=1
union all
select a.seqno,
a.amount,
if（b.t_amount>0 and a.seqno=b.seqno+1）a.amount
else a.amount+b.t_amount)
)select * from test2;
2年前
点赞 回复
大柏树
2年前
评论
0/*MogDB 3.0做的运行，跟pg一样，重在参与学习*/
/*创建表*/
create table test_tab(seqno integer, amount integer);
/*插入原始数据*/
insert into test_tab(seqno,amount) values (1,50),(2,-100),(3,20),(4,50 ),(5,-20 ),(6,-30),(7,4 ),(8,100),
(9,20 ),(10,-70),(11,40 );
/*实现*/
with recursive cte (seqno,amount,tot_amount) as(
select a.seqno,a.amount,a.amount tot_amount from test_tab a where a.seqno=1
union all
select a.seqno,
a.amount,
case
when b.tot_amount>0 then a.amount
else a.amount+b.tot_amount
end
from test_tab a join cte b on a.seqno=b.seqno+1
)select * from cte;
2年前
点赞 评论
寒冰
2年前
评论
0我使用的pg15 ，pg和其他数据不同 with如果使用递归的话需要加上RECURSIVE
WITH RECURSIVE cte_name AS(
cte_query_initial -- 初始化部分
UNION [ALL]
cte_query_iterative -- 递归部分
) SELECT * FROM cte_name;
create table test_tab(seqno integer, amount integer);
select * from test_tab;
insert into test_tab(seqno,amount) values
( 1 ,50),
(2,-100 ),
(3,20),
(4,50 ),
(5,-20 ),
(6,-30 ),
(7,40 ),
(8,100 ),
(9,20 ),
(10,-70),
(11,40 );
with recursive test_tmp (seqno,amount,tet_amount )
as (
select
t1.seqno,
t1.amount,
amount tet_amount
from
test_tab t1
where
t1.seqno = 1
union all
select
t1.seqno,
t1.amount,
case
when t2.tet_amount > 0 then t1.amount
else t1.amount + t2.tet_amount
end
from
test_tab t1
join test_tmp t2 on
t1.seqno = t2.seqno + 1
)
select * from test_tmp;
2年前
点赞 评论
锡
2年前
评论
0/*原始数据*/
drop table test_tab purge;
create table test_tab as
(
select 1 as seqno,50 as amount from dual
union all
select 2 as seqno,-100 as amount from dual
union all
select 3 as seqno,20 as amount from dual
union all
select 4 as seqno,50 as amount from dual
union all
select 5 as seqno,-20 as amount from dual
union all
select 6 as seqno,-30 as amount from dual
union all
select 7 as seqno,40 as amount from dual
union all
select 8 as seqno,100 as amount from dual
union all
select 9 as seqno,20 as amount from dual
union all
select 10 as seqno,-70 as amount from dual
union all
select 11 as seqno,40 as amount from dual
);
WITH tmp ( seqno, amount, tot_amount ) AS (
SELECT t1.seqno, t1.amount, amount tot_amount
FROM test_tab t1
WHERE t1.seqno = 1 UNION ALL
SELECT t1.seqno, t1.amount,
CASE WHEN t2.tot_amount > 0 THEN t1.amount ELSE t1.amount + t2.tot_amount END
FROM test_tab t1 join tmp t2 on t1.seqno = t2.seqno + 1
) SELECT * FROM tmp;
2年前
点赞 评论
查看更多 >
