select dDate,sun(X1)-sun(X2) from detail group by ddate order by ddate
cuo le ,,, select dDate,sum(X1)-sum(X2) as X-Y from detail group by ddate order by ddate
Declare @t table(dDate datetime) Declare @date datetime Set @date='2005-1-1' While @date<'2005-2-1' Begin Insert Into @t Select @date Set @date=DateAdd(d,1,@date) EndSelect dDate, (Select dDate,SUM(X1-X2) from Detail Where dDate<=A.dDate Group By dDate ) As [X1-X2] from @t A Order By dDate
declare @begindate datetime,@enddate datetime,@i int select @begindate='2005/1/1',@enddate='2005/3/1',@i= DATEDIFF(day, @begindate, @enddate) set rowcount @i select id=identity(int,0,1) into #t from sysobjects,syscolumns Select dateadd(day,id,@begindate) dDate, (Select isnull(SUM(X1-X2),0) from Detail Where dDate<=A.dDate) As [X1-X2] from #t A Order By dDatedrop table #t
create table Detail(dDate datetime, X1 int, X2 int )insert into Detail values('2005-1-2', 1000, 0 ) insert into Detail values('2005-1-9', 300, 200 ) insert into Detail values('2005-1-6', 1300, 0 ) insert into Detail values('2005-1-30', 200, 800) insert into Detail values('2005-1-21', 0, 100) insert into Detail values('2005-1-18', 400, 200) insert into Detail values('2005-1-15', 0, 500 )declare @begindate datetime,@enddate datetime,@i int select @begindate='2005/1/1',@enddate='2005/2/1',@i= DATEDIFF(day, @begindate, @enddate) set rowcount @i select id=identity(int,0,1) into #t from sysobjects,syscolumns set rowcount 0Select dateadd(day,A.id,@begindate) dDate ,(Select isnull(SUM(X1-X2),0) from Detail Where dDate<=dateadd(day,A.id,@begindate)) As [X1-X2] from #t A Order By dateadd(day,A.id,@begindate)drop table #t,Detail
from detail
group by ddate
order by ddate
select dDate,sum(X1)-sum(X2) as X-Y
from detail
group by ddate
order by ddate
Declare @t table(dDate datetime)
Declare @date datetime
Set @date='2005-1-1'
While @date<'2005-2-1'
Begin
Insert Into @t Select @date
Set @date=DateAdd(d,1,@date)
EndSelect dDate,
(Select dDate,SUM(X1-X2) from Detail Where dDate<=A.dDate Group By dDate ) As [X1-X2]
from @t A
Order By dDate
Create table Detail
(dDate DateTime,
X1 Int,
X2 Int)
--插入数据
Insert Detail Values('2005-1-2', 1000, 0 )
Insert Detail Values('2005-1-9', 300, 200)
Insert Detail Values('2005-1-6', 1300, 0 )
Insert Detail Values('2005-1-30', 200, 800)
Insert Detail Values('2005-1-21', 0, 100)
Insert Detail Values('2005-1-18', 400, 200)
Insert Detail Values('2005-1-15', 0, 500)
--测试
Declare @t table(dDate datetime)
Declare @date datetime
Set @date='2005-1-1'
While @date<'2005-2-1'
Begin
Insert Into @t Select @date
Set @date=DateAdd(d,1,@date)
End
Select Convert(Varchar(10),dDate,120) As dDate,
(Select IsNull(SUM(X1-X2),0) from Detail Where dDate<=A.dDate ) As [X1-X2]
from @t A
Order By dDate
--删除测试环境
Drop table Detail
--结果
/*
dDate X1-X2
2005-01-01 0
2005-01-02 1000
2005-01-03 1000
2005-01-04 1000
2005-01-05 1000
2005-01-06 2300
2005-01-07 2300
2005-01-08 2300
2005-01-09 2400
2005-01-10 2400
2005-01-11 2400
2005-01-12 2400
2005-01-13 2400
2005-01-14 2400
2005-01-15 1900
2005-01-16 1900
2005-01-17 1900
2005-01-18 2100
2005-01-19 2100
2005-01-20 2100
2005-01-21 2000
2005-01-22 2000
2005-01-23 2000
2005-01-24 2000
2005-01-25 2000
2005-01-26 2000
2005-01-27 2000
2005-01-28 2000
2005-01-29 2000
2005-01-30 1400
2005-01-31 1400
*/
select @begindate='2005/1/1',@enddate='2005/3/1',@i= DATEDIFF(day, @begindate, @enddate)
set rowcount @i
select id=identity(int,0,1) into #t from sysobjects,syscolumns Select dateadd(day,id,@begindate) dDate,
(Select isnull(SUM(X1-X2),0) from Detail Where dDate<=A.dDate) As [X1-X2]
from #t A Order By dDatedrop table #t
insert into Detail values('2005-1-9', 300, 200 )
insert into Detail values('2005-1-6', 1300, 0 )
insert into Detail values('2005-1-30', 200, 800)
insert into Detail values('2005-1-21', 0, 100)
insert into Detail values('2005-1-18', 400, 200)
insert into Detail values('2005-1-15', 0, 500 )declare @begindate datetime,@enddate datetime,@i int
select @begindate='2005/1/1',@enddate='2005/2/1',@i= DATEDIFF(day, @begindate, @enddate) set rowcount @i
select id=identity(int,0,1) into #t from sysobjects,syscolumns
set rowcount 0Select dateadd(day,A.id,@begindate) dDate
,(Select isnull(SUM(X1-X2),0) from Detail Where dDate<=dateadd(day,A.id,@begindate)) As [X1-X2]
from #t A Order By dateadd(day,A.id,@begindate)drop table #t,Detail