表
Members
字段
MemberId Crwated
1 2009-2-2 20:00:00
2 2009-2-2 21:10:00
3 2009-2-5 21:38:00
4 2009-3-1 20:00:00我传递参数开始日期和结束日期 比如开始时间是2009-02-01 结束日期是2009-02-28,查询结果这样
2009-02-01 0
2009-02-02 2
...
2009-02-02 1
..
2009-02-08 0
Members
字段
MemberId Crwated
1 2009-2-2 20:00:00
2 2009-2-2 21:10:00
3 2009-2-5 21:38:00
4 2009-3-1 20:00:00我传递参数开始日期和结束日期 比如开始时间是2009-02-01 结束日期是2009-02-28,查询结果这样
2009-02-01 0
2009-02-02 2
...
2009-02-02 1
..
2009-02-08 0
解决方案 »
- 求教关于邮件的问题!!!!!!!!!!!
- vs2005开发的东西放到.net1.0上
- 安装vstf问题
- vs2005里美工做不来,谁能给指点下
- [求助]关于页面上多个验证控件的问题!
- ASP.NET中如何用COOKIES判断用户是否登陆?
- GridView 使用,SqlDataSource的selectcommand使用存储过程时.怎么搞
- 怎样在ASP.NET中将DataGrid导入Excel中,
- *_^ !!!!水晶报表的问题:数据库里面的内容有5000个字,可浏览的时候只显示出了1000多个字,还有好多的字都没有显示!!急,在线等~~~
- 服务器调用另一台机器上的文件?
- 程序员面试之葵花宝典
- 高手进,200分求
group by convert(varchar(10),Crwated,101)
2009-2-5 1
2009-3-1 1没显示的日期Crwated就是0
declare @end DATETIME
set @begin = '2009-03-02'
set @end = '2009-03-03'select count(*),convert(varchar(10),Crwated ,120) from Members
where DATEDIFF(d, Crwated , @begin) <=0 and DATEDIFF(d, Crwated , @end) >=0
group by convert(varchar(10),Crwated ,120)
insert into @tbl
select 1 ,'2009-2-2 23:00:00' union all
select 2 ,'2009-2-2 21:10:00' union all
select 3 ,'2009-2-5 21:38:00' union all
select 4 ,'2009-3-1 20:00:00'
--select * from @tbl
declare @min datetime
declare @max datetime
select @min=min(Crwated) from @tbl
select @max=max(Crwated) from @tbl
declare @tbl2 table(date datetime)
while(datediff(d,@min,@max)>=0)
begin
insert into @tbl2 values(@min)
set @min = dateadd(d,1,@min)
endselect convert(varchar(10),date,120),
sum(case when MemberId is null then 0 else 1 end) [注册数]
from @tbl2 a
left join @tbl b on datediff(d,a.date,b.Crwated)=0
group by convert(varchar(10),date,120)
select convert(varchar(10),date,120) [日期],
sum(case when MemberId is null then 0 else 1 end) [注册数]
from @tbl2 a
left join @tbl b on datediff(d,a.date,b.Crwated)=0
where convert(varchar(10),date,120) between '2009-02-01' and '2009-02-28'
group by convert(varchar(10),date,120)
if object_id('[Members]') is not null drop table [Members]
create table [Members] (MemberId int,Crwated datetime)
insert into [Members]
select 1,'2009-2-2 20:00:00' union all
select 2,'2009-2-2 21:10:00' union all
select 3,'2009-2-5 21:38:00' union all
select 4,'2009-3-1 20:00:00'create proc GetCount
@bdate datetime,
@edate datetime
as
declare @i table(dt datetime)
while(@bdate<=@edate)
begin
insert into @I select @bdate
set @bdate=dateadd(dd,1,@bdate)
end
select dt,数量=(select count(1) from members where datediff(dd,crwated,a.dt)>=0)
from @i a
go--执行:
exec getcount @bdate='2009-02-01',@edate='2009-02-28'
declare @tbl table (MemberId int,Crwated datetime)
insert into @tbl
select 1 ,'2009-2-2 23:00:00' union all
select 2 ,'2009-2-2 21:10:00' union all
select 3 ,'2009-2-5 21:38:00' union all
select 3 ,'2009-2-28 21:38:00' union all
select 4 ,'2009-3-1 20:00:00' declare @min datetime
declare @max datetime
select @min='2009-02-01' --这里设置时间段
select @max='2009-02-28'
declare @tbl2 table(date datetime)
while(datediff(d,@min,@max)>=0)
begin
insert into @tbl2 values(@min)
set @min = dateadd(d,1,@min)
end
select convert(varchar(10),date,120) [日期],
sum(case when MemberId is null then 0 else 1 end) [注册数]
from @tbl2 a
left join @tbl b on datediff(d,a.date,b.Crwated)=0
group by convert(varchar(10),date,120)
declare @begin datetime
declare @end datetime
declare @sql varchar(8000)
set @sql=''
set @begin = '2009-02-01'
set @end = '2009-02-28'
while @begin < @end
begin
set @sql=@sql+'select '+convert(varchar(50),@begin,112)+',count(MemberId) from Members where convert(varchar(50),Created,112)=convert(varchar(50),'+convert(varchar(50),@begin,112)+',112) union '
set @begin=dateadd(day,1,@begin)
end
set @sql=@sql+'select '+convert(varchar(50),@begin,112)+',count(MemberId) from Members where convert(varchar(50),Created,112)=convert(varchar(50),'+convert(varchar(50),@begin,112)+',112)'
exec (@sql)