按日期统计的问题
有这么一个表
CreateDate Title ...
2007-1-1 11:10:00 t1
2007-1-1 13:00:00 t2
2007-1-3 07:01:01 t4
.
.
.
2007-3-1 10:10:10 t44
我希望做这么一个统计，到每天为止一共有多少条，即为
2007-1-1 2
2007-1-2 2
2007-1-3 5
.
.
.
每天都要把之前发生的都统计进去
[解决办法]
create table #(CreateDate datetime, Title varchar(20))
insert into # select '2007-1-1 11:10:00 ' , 't1 ' union all
select '2007-1-1 13:00:00 ' , 't2 ' union all
select '2007-1-3 07:01:01 ' , 't4 '
select convert(varchar(10),CreateDate,120) as a,count(1) as b into ## from # group by convert(varchar(10),CreateDate,120)
select a,isnull((select sum(b) from ## where a <t.a),0) from ## t group by a
-----
2007-01-010
2007-01-032
[解决办法]
有日期应该都有记录，不需要ISNULL判断了吧？
select a,(select sum(b) from #xx where a <=t.a) from #xx t group by a
