sql求连续值
use [tempdb]
create table tbs
(
Ftime datetime,
number int
)
insert into tbs select '20051102' ,5020
union all select '20051103' ,2724
union all select '20051104' ,2432
union all select '20051105' ,2237
union all select '20051106' ,2510
union all select '20051107' ,1384
union all select '20051108' ,1074
union all select '20051109' ,1191
union all select '20051110' ,1253
union all select '20051111' ,1383
union all select '20051112' ,1532
union all select '20051113' ,1490
union all select '20051114' ,1213
union all select '20051115' ,1578
union all select '20051116' ,300
with t as
(
select *, case when isnull((select a.number-b.number from tbs as b where 1=DATEDIFF(DAY,b.Ftime,a.Ftime)),a.number)>=0 then 1 else 0 end as 辅助列一
from tbs as a
),tt as
(
select *,day(Ftime)-row_number() over(partition by 辅助列一 order by Ftime) as 辅助列二
from t
),ttt as
(
select 辅助列一, count(Ftime) as 次数
from tt
group by 辅助列一,辅助列二
)
select case 辅助列一 when 1 then '连续涨' else '连续跌' end as 状态,max(次数) as 最大连续
from ttt
group by 辅助列一
