根据时间算出每天数量的总和

问题 

表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

实现方法：

先利用 master..spt_values 表重构日期 ，再将列转成行，最后进行左连。

测试建表及录入测试数据：

create table HKB
(
ID int identity(1,1) not null,
Ntime varchar(20) null,
Nanji int null,
Suzhou int null
)

insert HKB(Ntime,Nanji,Suzhou)
select '2012-1-10', 20, 30
union all
select '2012-1-10', 24, 18
union all
select '2012-1-18', 30, 12
union all
select '2012-1-19', 22, 32
union all
select '2012-1-31', 40, 32

 

/*

   利用 master..spt_values 表重构日期表 ，生成临时表 #t1

*/

select convert(dateadd(day,number,'2012-1-1') dt ,b.uname into #t1 from master..spt_values,
(select 'Nanji'[uname] union all select 'Suzhou') b

where number<day(dateadd(day,-1,(convert(varchar(7),dateadd(month,1,'2012-1-15'),120)+'-01')))
and type='p'
order by b.uname

/*

   列转行代码

select convert(datetime, Ntime)Ntime,'Nanji' [uname],sum(Nanji)uno from hkb group by ntime
union all
select convert(datetime, Ntime)Ntime,'Suzhou' [uname],sum(Suzhou)uno from hkb group by ntime

*/

 

/*

   左连得出最终结果

*/

select datepart(dd,#t1.dt)dt,#t1.uname,isnull(b.uno,0)uno from #t1
left join (
select convert(datetime, Ntime)Ntime,'Nanji' [uname],sum(Nanji)uno from hkb group by ntime
union all
select convert(datetime, Ntime)Ntime,'Suzhou' [uname],sum(Suzhou)uno from hkb group by ntime
)b
on #t1.uname=b.uname and #t1.dt=b.Ntime

/*  结果*/

myday  uname  uno

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

1 Nanji 0
2 Nanji 0
3 Nanji 0
4 Nanji 0
5 Nanji 0
6 Nanji 0
7 Nanji 0
8 Nanji 0
9 Nanji 0
10 Nanji 44
11 Nanji 0
12 Nanji 0
13 Nanji 0
14 Nanji 0
15 Nanji 0
16 Nanji 0
17 Nanji 0
18 Nanji 30
19 Nanji 22
20 Nanji 0
21 Nanji 0
22 Nanji 0
23 Nanji 0
24 Nanji 0
25 Nanji 0
26 Nanji 0
27 Nanji 0
28 Nanji 0
29 Nanji 0
30 Nanji 0
31 Nanji 40
1 Suzhou 0
2 Suzhou 0
3 Suzhou 0
4 Suzhou 0
5 Suzhou 0
6 Suzhou 0
7 Suzhou 0
8 Suzhou 0
9 Suzhou 0
10 Suzhou 48
11 Suzhou 0
12 Suzhou 0
13 Suzhou 0
14 Suzhou 0
15 Suzhou 0
16 Suzhou 0
17 Suzhou 0
18 Suzhou 12
19 Suzhou 32
20 Suzhou 0
21 Suzhou 0
22 Suzhou 0
23 Suzhou 0
24 Suzhou 0
25 Suzhou 0
26 Suzhou 0
27 Suzhou 0
28 Suzhou 0
29 Suzhou 0
30 Suzhou 0
31 Suzhou 32