原数据:
Type Time
1 2011-02-01 15:00:00.000
1 2011-02-01 16:00:00.000
1 2011-02-01 17:00:00.000
2 2011-02-01 18:00:00.000
3 2011-02-01 19:00:00.000
1 2011-02-01 20:00:00.000
1 2011-02-01 21:00:00.000
1 2011-02-01 22:00:00.000
3 2011-02-02 08:00:00.000
1 2011-02-02 08:10:00.000
1 2011-02-02 08:40:00.000
1 2011-02-02 10:25:00.000
3 2011-02-02 10:54:00.000
2 2011-02-02 11:00:00.000
2 2011-02-02 13:00:00.000
2 2011-02-03 08:00:00.000
2 2011-02-04 12:00:00.000
1 2011-02-05 15:00:00.000
1 2011-02-08 22:00:00.000
1 2011-02-09 10:00:00.000
1 2011-02-09 15:00:00.000
1 2011-02-09 17:00:00.000
1 2011-02-09 22:00:00.000
1 2011-02-10 10:00:00.000
想要这样的数据:(Num列就是当天类型是1的条数,没有的日期要补上,条数为0即可)
Num DateTime
6 2011-02-01
3 2011-02-02
0 2011-02-03
0 2011-02-04
1 2011-02-05
0 2011-02-06
0 2011-02-07
1 2011-02-08
4 2011-02-09
1 2011-02-10
大侠们都来帮帮忙!
Type Time
1 2011-02-01 15:00:00.000
1 2011-02-01 16:00:00.000
1 2011-02-01 17:00:00.000
2 2011-02-01 18:00:00.000
3 2011-02-01 19:00:00.000
1 2011-02-01 20:00:00.000
1 2011-02-01 21:00:00.000
1 2011-02-01 22:00:00.000
3 2011-02-02 08:00:00.000
1 2011-02-02 08:10:00.000
1 2011-02-02 08:40:00.000
1 2011-02-02 10:25:00.000
3 2011-02-02 10:54:00.000
2 2011-02-02 11:00:00.000
2 2011-02-02 13:00:00.000
2 2011-02-03 08:00:00.000
2 2011-02-04 12:00:00.000
1 2011-02-05 15:00:00.000
1 2011-02-08 22:00:00.000
1 2011-02-09 10:00:00.000
1 2011-02-09 15:00:00.000
1 2011-02-09 17:00:00.000
1 2011-02-09 22:00:00.000
1 2011-02-10 10:00:00.000
想要这样的数据:(Num列就是当天类型是1的条数,没有的日期要补上,条数为0即可)
Num DateTime
6 2011-02-01
3 2011-02-02
0 2011-02-03
0 2011-02-04
1 2011-02-05
0 2011-02-06
0 2011-02-07
1 2011-02-08
4 2011-02-09
1 2011-02-10
大侠们都来帮帮忙!
,count(case when type=1 then 1 end) as num
from tb
group by convert(varchar(10),time,120)
from tb
where type=1
group by convert(varchar(10),time,120)
drop table tb
Go
Create table tb([Type] int,[Time] Datetime)
Insert tb
select 1,'2011-02-01 15:00:00.000' union all
select 1,'2011-02-01 16:00:00.000' union all
select 1,'2011-02-01 17:00:00.000' union all
select 2,'2011-02-01 18:00:00.000' union all
select 3,'2011-02-01 19:00:00.000' union all
select 1,'2011-02-01 20:00:00.000' union all
select 1,'2011-02-01 21:00:00.000' union all
select 1,'2011-02-01 22:00:00.000' union all
select 3,'2011-02-02 08:00:00.000' union all
select 1,'2011-02-02 08:10:00.000' union all
select 1,'2011-02-02 08:40:00.000' union all
select 1,'2011-02-02 10:25:00.000' union all
select 3,'2011-02-02 10:54:00.000' union all
select 2,'2011-02-02 11:00:00.000' union all
select 2,'2011-02-02 13:00:00.000' union all
select 2,'2011-02-03 08:00:00.000' union all
select 2,'2011-02-04 12:00:00.000' union all
select 1,'2011-02-05 15:00:00.000' union all
select 1,'2011-02-08 22:00:00.000' union all
select 1,'2011-02-09 10:00:00.000' union all
select 1,'2011-02-09 15:00:00.000' union all
select 1,'2011-02-09 17:00:00.000' union all
select 1,'2011-02-09 22:00:00.000' union all
select 1,'2011-02-10 10:00:00.000'
Go
declare @dtmin datetime,@dtmax datetime
select @dtmin=min([Time]),@dtmax=max([Time])from tb
;with tt
as(select convert(varchar(10),time,120)[time],
count(case when type=1 then 1 end)num
from tb
group by convert(varchar(10),time,120)
)
select convert(varchar(10),dateadd(day,number,@dtmin),120)[time],
isnull(b.num,0)[num]
from master..spt_values a left join tt b
on convert(varchar(10),dateadd(day,number,@dtmin),120)=b.[time]
where type='P' and datediff(day,dateadd(day,number,@dtmin),@dtmax)>=0
/*
time num
---------- -----------
2011-02-01 6
2011-02-02 3
2011-02-03 0
2011-02-04 0
2011-02-05 1
2011-02-06 0
2011-02-07 0
2011-02-08 1
2011-02-09 4
2011-02-10 1
警告: 聚合或其他 SET 操作消除了空值。(10 行受影响)
*/
--#5的演示数据
with dt as
(
select d = dateadd(d,number,'2011-02-01') from master..spt_values where dateadd(d,number,'2011-02-01') <= '2011-02-10' and type = 'p'
)select num = isnull(b.num,0),a.d
from dt a
left join (select [time] = convert(varchar(10),[time],120), num = count(1) from tb where [type]=1 group by convert(varchar(10),[time],120)) b on a.d = b.[time]num d
----------- -----------------------
6 2011-02-01 00:00:00.000
3 2011-02-02 00:00:00.000
0 2011-02-03 00:00:00.000
0 2011-02-04 00:00:00.000
1 2011-02-05 00:00:00.000
0 2011-02-06 00:00:00.000
0 2011-02-07 00:00:00.000
1 2011-02-08 00:00:00.000
4 2011-02-09 00:00:00.000
1 2011-02-10 00:00:00.000(10 行受影响)
http://blog.csdn.net/xys_777/archive/2010/06/20/5681208.aspx
仅参考
Insert tb
select 1,'2011-02-01 15:00:00.000' union all
select 1,'2011-02-01 16:00:00.000' union all
select 1,'2011-02-01 17:00:00.000' union all
select 2,'2011-02-01 18:00:00.000' union all
select 3,'2011-02-01 19:00:00.000' union all
select 1,'2011-02-01 20:00:00.000' union all
select 1,'2011-02-01 21:00:00.000' union all
select 1,'2011-02-01 22:00:00.000' union all
select 3,'2011-02-02 08:00:00.000' union all
select 1,'2011-02-02 08:10:00.000' union all
select 1,'2011-02-02 08:40:00.000' union all
select 1,'2011-02-02 10:25:00.000' union all
select 3,'2011-02-02 10:54:00.000' union all
select 2,'2011-02-02 11:00:00.000' union all
select 2,'2011-02-02 13:00:00.000' union all
select 2,'2011-02-03 08:00:00.000' union all
select 2,'2011-02-04 12:00:00.000' union all
select 1,'2011-02-05 15:00:00.000' union all
select 1,'2011-02-08 22:00:00.000' union all
select 1,'2011-02-09 10:00:00.000' union all
select 1,'2011-02-09 15:00:00.000' union all
select 1,'2011-02-09 17:00:00.000' union all
select 1,'2011-02-09 22:00:00.000' union all
select 1,'2011-02-10 10:00:00.000'--第一种方法,做个临时表或子查询
select m.time , isnull(n.num , 0) num from
(
select '2011-02-01' Time union all
select '2011-02-02' Time union all
select '2011-02-03' Time union all
select '2011-02-04' Time union all
select '2011-02-05' Time union all
select '2011-02-06' Time union all
select '2011-02-07' Time union all
select '2011-02-08' Time union all
select '2011-02-09' Time union all
select '2011-02-10' Time
) m left join
(
select convert(varchar(10),Time,120) time,count(1) Num from tb group by convert(varchar(10),Time,120)
) n
on m.time = n.time
/*
time num
---------- -----------
2011-02-01 8
2011-02-02 7
2011-02-03 1
2011-02-04 1
2011-02-05 1
2011-02-06 0
2011-02-07 0
2011-02-08 1
2011-02-09 4
2011-02-10 1(所影响的行数为 10 行)
*/--第二种方法,利用sql server的系统内容。
select m.time , isnull(n.num , 0) num from
(
select
convert(varchar(10),dateadd(dd,num,'2011-02-01'),120) time
from
(select isnull((select count(1) from sysobjects where id<t.id),0) as num from sysobjects t) a
where
dateadd(dd,num,'2011-02-01') <= '2011-02-10'
) m left join
(
select convert(varchar(10),Time,120) time,count(1) Num from tb group by convert(varchar(10),Time,120)
) n
on m.time = n.time
/*
time num
---------- -----------
2011-02-01 8
2011-02-02 7
2011-02-03 1
2011-02-04 1
2011-02-05 1
2011-02-06 0
2011-02-07 0
2011-02-08 1
2011-02-09 4
2011-02-10 1(所影响的行数为 10 行)
*/
drop table tb