日期:2014-05-18  浏览次数:20472 次

sql语句求连续盈亏的天数


加入要根据前面连个字段求出后面一个字段,怎么写程序?

------解决方案--------------------
SQL code

-- 建表
create table ta(dt datetime, n int);
insert into ta(dt, n)
select '2112-1-1', -39  union all
select '2112-1-2', -8  union all
select '2112-1-3', 32  union all
select '2112-1-4', -3  union all
select '2112-1-5', -39  union all
select '2112-1-6', -32  union all
select '2112-1-7', 40  union all
select '2112-1-8', 20  union all
select '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