表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)<[email protected]) 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)<[email protected]) 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!