sql语句求连续盈亏的天数
加入要根据前面连个字段求出后面一个字段,怎么写程序?
[解决办法]
-- 建表create table ta(dt datetime, n int);insert into ta(dt, n)select '2112-1-1', -39 union allselect '2112-1-2', -8 union allselect '2112-1-3', 32 union allselect '2112-1-4', -3 union allselect '2112-1-5', -39 union allselect '2112-1-6', -32 union allselect '2112-1-7', 40 union allselect '2112-1-8', 20 union allselect '2112-1-9', -25-- 查询连续盈亏数;with t as( select a.r, a.dt, a.n, a.d from ( select r=row_number() over (order by dt asc), dt, n, d=case when n<0 then -1 else 1 end from ta ) a where a.r=1 union all select b.r, b.dt, b.n, d=case when b.n<0 and t.d<0 then t.d-1 when b.n<0 and t.d>=0 then -1 when b.n>=0 and t.d<0 then 1 when b.n>=0 and t.d>=0 then t.d+1 end from ( select r=row_number() over (order by dt asc), dt, n from ta ) b, t where b.r=t.r+1)select * from t