根据时间算出每天数量的总和,该如何处理

根据时间算出每天数量的总和
表HKB
 Nanji和Suzhou均为int类型


ID Ntime Nanji Suzhou

1 2012-1-10 20 30

2 2012-1-10 24 18

3 2012-1-18 30 12

4 2012-1-19 22 32


...

31 2012-1-31 40 32



要得到的结果是

UNAME Myday UNO  

Nanji 1 0
...

Nanji 10 44 

Nanji 18 30

Nanji 19 22

...

Nanji 31 40 
 
Suzhou 1 0

...

Suzhou 10 48

...

Suzhou 31 32

   


[解决办法]

SQL code

--sql 2000declare @sdate datetimedeclare @edate datetimeset @sdate = '2012-01-01'set @edate = '2012-01-31'select uname = 'nanji' , m.dt , isnull(n.nanji) uno from(select     dateadd(dd,num,@sdate) dtfrom     (select isnull((select count(1) from sysobjects where id<t.id),0) as num from sysobjects t) awhere    dateadd(dd,num,@sdate)<=@edate) mleft join(  select convert(varchar(10),Ntime,120) dt , sum(Nanji) Nanji from hkb group by convert(varchar(10),Ntime,120)) non m.dt = n.dtunion allselect uname = 'Suzhou' , m.dt , isnull(n.Suzhou) uno from(select     dateadd(dd,num,@sdate) dtfrom     (select isnull((select count(1) from sysobjects where id<t.id),0) as num from sysobjects t) awhere    dateadd(dd,num,@sdate)<=@edate) mleft join(  select convert(varchar(10),Ntime,120) dt , sum(Suzhou) Suzhou from hkb group by convert(varchar(10),Ntime,120)) non m.dt = n.dt
[解决办法]
如需要阅读该回复，请登录或注册CSDN!