27,579
社区成员
发帖
与我相关
我的任务
分享
终于搞出来了。感觉着表结构设计的让人心碎啊
with cte as
(select rec_date, case when convert(int,recordtime)<800
then rec_date-1 else rec_date end as rec_date1,recordtime,dayrf from test),
cte1 as
(select rec_date,dayrf from test where recordtime='800'),
cte2 as
(select A.REC_DATE,SUM(convert(int,B.DAYRF)) AS DAYSUM from cte1 as a join cte1 as b on a.rec_date>=b.rec_DATE
group by a.rec_date),
cte3 as
(select rec_date,rec_date1,recordtime,case when recordtime='800' then '0' else dayrf end as dayrf from cte )
select rec_date1,recordtime,dayrf+DAYSUM as daysum from cte3 join cte2 on cte2.rec_date=cte3.rec_date1
--结果,你要求任何2个时间点的间隔只要把这2个时间点的数值相减就好了。
rec_date1 recordtime daysum
----------- ---------- -----------
140924 800 100
140924 900 100
140924 1000 100
140924 1100 200
140924 2300 200
140924 600 200
140925 800 200
140925 900 200
140925 1200 300
140925 2000 400
140926 800 400