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

根据时间算出每天数量的总和
表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 2000
declare @sdate datetime
declare @edate datetime
set @sdate = '2012-01-01'
set @edate = '2012-01-31'

select uname = 'nanji' , m.dt , isnull(n.nanji) uno from
(
select 
    dateadd(dd,num,@sdate) dt
from 
    (select isnull((select count(1) from sysobjects where id<t.id),0) as num from sysobjects t) a
where
    dateadd(dd,num,@sdate)<=@edate
) m
left join
(
  select convert(varchar(10),Ntime,120) dt , sum(Nanji) Nanji from hkb group by convert(varchar(10),Ntime,120)
) n
on m.dt = n.dt
union all
select uname = 'Suzhou' , m.dt , isnull(n.Suzhou) uno from
(
select 
    dateadd(dd,num,@sdate) dt
from 
    (select isnull((select count(1) from sysobjects where id<t.id),0) as num from sysobjects t) a
where
    dateadd(dd,num,@sdate)<=@edate
) m
left join
(
  select convert(varchar(10),Ntime,120) dt , sum(Suzhou) Suzhou from hkb group by convert(varchar(10),Ntime,120)
) n
on m.dt = n.dt

------解决方案--------------------
如需要阅读该回复，请登录或注册****!