表格如下:id time num
1 2010-1-30 9:01 2
1 2010-1-30 9:02 1
1 2010-1-31 9:03 3
1 2010-1-31 9:04 5
1 2010-2-1 0:01 22 2010-1-31 9:01 1
2 2010-1-31 9:04 4
.. .. ..num是不定的整数,时间间隔也不定,希望得到如下表格:1 2010-1-31 3
1 2010-2-1 112 3010-2-1 5
.. .. ..除了第一行,以下都是累加num
1 2010-1-30 9:01 2
1 2010-1-30 9:02 1
1 2010-1-31 9:03 3
1 2010-1-31 9:04 5
1 2010-2-1 0:01 22 2010-1-31 9:01 1
2 2010-1-31 9:04 4
.. .. ..num是不定的整数,时间间隔也不定,希望得到如下表格:1 2010-1-31 3
1 2010-2-1 112 3010-2-1 5
.. .. ..除了第一行,以下都是累加num
FROM TB T WHERE EXISTS(SELECT 1 FROM TB WHERE ID=T.ID AND T.TIME>TIME)
GROUP BY ID,CONVERT(VARCHAR(10),TIME,120)
(select sum(num) from tb where time < t.time and id = t.id)
from tb t
declare @tb table([id] int,[time] datetime,[num] int)
insert @tb
select 1,'2010-1-30 9:01',2 union all
select 1,'2010-1-30 9:02',1 union all
select 1,'2010-1-31 9:03',3 union all
select 1,'2010-1-31 9:04',5 union all
select 1,'2010-2-1 0:01',2 union all
select 2,'2010-1-31 9:01',1 union all
select 2,'2010-1-31 9:04',4SELECT ID,CONVERT(VARCHAR(10),DATEADD(DD,1,[time]),120)AS [time], SUM([num]) AS [num] FROM @tb a
GROUP BY [id],CONVERT(VARCHAR(10),DATEADD(DD,1,[time]),120)
/*
ID time num
----------- ---------- -----------
1 2010-01-31 3
1 2010-02-01 8
2 2010-02-01 5
1 2010-02-02 2(4 行受影响)
*/
每天的都有数据量,不考虑计算每天:
SELECT ID,CONVERT(VARCHAR(10),TIME,120) TIME,SUM(NUM)
FROM TB
GROUP BY ID,CONVERT(VARCHAR(10),TIME,120)
(select sum(num) from tb where
CONVERT(VARCHAR(10),TIME,120)< CONVERT(VARCHAR(10),T.TIME,120) and id = t.id)
from tb t 楼主就自己修改一下就行
(select sum(num) from tb where id=r.id and convert(varchar(10),[time],120) < r.[time]) as num
from
(
select id,convert(varchar(10),[time],120) as [time]
from tb t
group by id,convert(varchar(10),[time],120)
) r