sql 根据日期累加数据问题求助

pcsue 2009-11-20 09:05:23

我有一个表totle,结构如下
day number
2009-10-11 5

2009-10-12 7

2009-11-11 8

想要的输出结果：

date number
2009-10-11 5

2009-10-12 12

2009-11-11 20

就是number 的值不断累加

下面是我自己写的sql
但不知道为什么，number累加的值不正确，请问我这样写有什么问题吗？请各位指点一下，谢谢啦
select
a.*,
(select sum(number)
from
totle as b
where b.open_date<=a.open_date

) as nums
from totle as a
where

...全文

548 17 打赏收藏转发到动态举报

写回复

用AI写文章

17 条回复

切换为时间正序

请发表友善的回复…

发表回复

pcsue 2009-11-24

打赏
举报

谢谢大家给我的思路，已经做好了

linbiao523 2009-11-23

打赏
举报



create table tb([day] datetime,[number] int)

insert into tb

select '2009-10-11',5 union all

select '2009-10-12',7 union all

select '2009-11-11',8 union all

select '2008-11-11',8



select identity(int,1,1) id ,[day],number into #Temp from tb 



select [day] , number = (select sum(number) from #Temp where id <= t.id) from  #Temp t



drop table tb



drop table #Temp

gaomiqzhi 2009-11-23

打赏
举报

[Quote=引用 9 楼 pcsue 的回复:]
引用 5 楼 fredrickhu 的回复:
最好加个自增列因为时间不一定是递增的

问题就是我的表没有id
而且各位的方法我也试过，统计的数据还是不正确
number 递增结果
1 17
1 38
1 42
1 44
1 45
1 48
1 49
1 51
1 56
1 62
1 63
1 65
1 69
2 74
2 102
1 121
1 139
2 165
1 198
1 252
1 254

[/Quote]

用临时表

tcm441224375 2009-11-23

打赏
举报

学习，学习

午夜还在张 2009-11-23

打赏
举报

create table totle
(totle_day datetime,
totle_number int)

insert totle select '2009-10-11',5
union all
select '2009-10-12',7
union all
select '2009-11-11',8

select a.totle_day ,sum(b.totle_number) as count_number from totle a , totle b where b.totle_day<=a.totle_day
group by a.totle_day

nianran520 2009-11-23

打赏
举报



--> 测试数据：@table

declare @table table([day] varchar(10),[number] int)

insert @table

select '2009-10-11',5 union all

select '2009-10-12',7 union all

select '2009-11-11',8



select t.day,sum(h.number) as number

from @table t join  @table h

on convert(varchar(10),h.day,120) <= convert(varchar(10),t.day,120)

group by t.day

--结果

--------------------------

2009-10-11	5

2009-10-12	12

2009-11-11	20

pcsue 2009-11-22

打赏
举报

如果有重复的日期的话，各位提供给我的做法计算的结果还是有问题
继续救助中。。。。。

dla001 2009-11-20

打赏
举报

 抄dawugui的 加入2008时间

create table tb([day] datetime,[number] int)

insert into tb

select '2009-10-11',5 union all

select '2009-10-12',7 union all

select '2009-11-11',8 union all

select '2008-11-11',8



select [day] , number = (select sum(number) from tb where [day] <= t.[day]) from tb t



drop table tb

dawugui的是对的！
不好看就order by day就好看了

pcsue 2009-11-20

打赏
举报

[Quote=引用 5 楼 fredrickhu 的回复:]
最好加个自增列因为时间不一定是递增的
[/Quote]

问题就是我的表没有id
而且各位的方法我也试过，统计的数据还是不正确
number 递增结果
1 17
1 38
1 42
1 44
1 45
1 48
1 49
1 51
1 56
1 62
1 63
1 65
1 69
2 74
2 102
1 121
1 139
2 165
1 198
1 252
1 254

--小F-- 2009-11-20

打赏
举报

----------------------------------------------------------------

-- Author  :fredrickhu(小F，向高手学习)

-- Date    :2009-11-20 21:09:19

-- Version:

--      Microsoft SQL Server 2005 - 9.00.4035.00 (Intel X86) 

--	Nov 24 2008 13:01:59 

--	Copyright (c) 1988-2005 Microsoft Corporation

--	Developer Edition on Windows NT 5.1 (Build 2600: Service Pack 3)

--

----------------------------------------------------------------

--> 测试数据：[tb]

if object_id('[tb]') is not null drop table [tb]

go 

create table [tb]([day] datetime,[number] int)

insert [tb]

select '2009-10-11',5 union all

select '2009-10-12',7 union all

select '2009-11-11',8

--------------开始查询--------------------------

select

 t.[day],

(select sum(number) from (select *,id=row_number()over(order by getdate()) from tb) as b where [day] <=t.[day] ) as number 

from 

(select *,id=row_number()over(order by getdate()) from tb) t 





----------------结果----------------------------

/* day                     nums

----------------------- -----------

2009-10-11 00:00:00.000 5

2009-10-12 00:00:00.000 12

2009-11-11 00:00:00.000 20



(3 行受影响)



*/

dla001 2009-11-20

打赏
举报

学习学习！

dawugui 2009-11-20

打赏
举报

create table tb([day] datetime,[number] int)

insert into tb

select '2009-10-11',5 union all

select '2009-10-12',7 union all

select '2009-11-11',8



select [day] , number = (select sum(number) from tb where [day] <= t.[day]) from tb t



drop table tb



/*

day                                                    number      

------------------------------------------------------ ----------- 

2009-10-11 00:00:00.000                                5

2009-10-12 00:00:00.000                                12

2009-11-11 00:00:00.000                                20



（所影响的行数为 3 行）



*/

--小F-- 2009-11-20

打赏
举报

最好加个自增列因为时间不一定是递增的

bancxc 2009-11-20

打赏
举报

if object_ID('totle') is not null drop table totle

go

create table totle([day] datetime,number int)

go

insert into totle values('2009-10-11',5)

insert into totle values('2009-10-12 ',7)

insert into totle values('2009-11-11',8)



select a.*, (select sum(number) from totle as b where b.[day] <=a.[day] ) as nums 

from totle as a 



/*day                     number      nums

----------------------- ----------- -----------

2009-10-11 00:00:00.000 5           5

2009-10-12 00:00:00.000 7           12

2009-11-11 00:00:00.000 8           20



(3 行受影响)



*/

icelovey 2009-11-20

打赏
举报

楼主不要SELECT *

icelovey 2009-11-20

打赏
举报



declare @tb table([day] datetime,[number] int)

insert @tb

select '2009-10-11',5 union all

select '2009-10-12',7 union all

select '2009-11-11',8



select [day],(select sum([number]) from @tb where t.[day]>=[day])

from @tb t

group by [day]



/*

day                                                                

------------------------------------------------------ ----------- 

2009-10-11 00:00:00.000                                5

2009-10-12 00:00:00.000                                12

2009-11-11 00:00:00.000                                20



（所影响的行数为 3 行）