--建立测试环境
Create Table 表(id varchar(10),regTime datetime,unregTime datetime)
--插入数据
insert into 表
select '1','2005-01-01','2005-01-02' union
select '2','2005-01-01','2005-01-04' union
select '3','2005-01-01','2005-01-05' union
select '4','2005-01-01','2005-01-04' union
select '5','2005-01-02','2005-01-06' union
select '6','2005-01-02','2005-01-04' union
select '7','2005-01-02','2005-01-03' union
select '8','2005-01-03','2005-01-04' union
select '9','2005-01-04','2005-01-08' union
select '10','2005-01-05','2005-01-06'
select * from 表
--测试语句
select regcount=sum(case when datediff(dd,regtime,'2005-1-1')=0 then 1 else 0 end),
unregcount=sum(case when datediff(dd,'2005-1-1',unregtime)=3 then 1 else 0 end)
from 表
--删除测试环境
Drop Table 表
Create Table 表(id varchar(10),regTime datetime,unregTime datetime)
--插入数据
insert into 表
select '1','2005-01-01','2005-01-02' union
select '2','2005-01-01','2005-01-04' union
select '3','2005-01-01','2005-01-05' union
select '4','2005-01-01','2005-01-04' union
select '5','2005-01-02','2005-01-06' union
select '6','2005-01-02','2005-01-04' union
select '7','2005-01-02','2005-01-03' union
select '8','2005-01-03','2005-01-04' union
select '9','2005-01-04','2005-01-08' union
select '10','2005-01-05','2005-01-06'
select * from 表
--测试语句
select regcount=sum(case when datediff(dd,regtime,'2005-1-1')=0 then 1 else 0 end),
unregcount=sum(case when datediff(dd,'2005-1-1',unregtime)=3 then 1 else 0 end)
from 表
--删除测试环境
Drop Table 表
select @date='2005-03-02'select * from yourtable where convert(varchar(10),regTime,111)=@date and unregTime between @date and @date+3
select @date='2005-03-02'select * from yourtable where convert(varchar(10),regTime,111)=@date and unregTime between @date and @date+3
,三天后注销人数=(select count(*) from tablename
where unregTime>=dateadd(day,3,a.UnregTime))
,三天内注销人数=(select count(*) from tablename
where unregTime<=dateadd(day,3,a.UnregTime))
from tablename
set @regTime='2005-03-02'
select
sum(case when regTime=@regTime then 1 else 0 end) as 注册人数,
sum(case when regtime between @regTime and dateadd(day,3,@regTime) then 1 else 0 end) 3天内注销人数
from regTable
select @date='2005-03-02'select count(*) from yourtable where convert(varchar(10),regTime,111)=@date and unregTime between @date and @date+3
,(select count(unregTime) as unregc from 用户表 where unregTime between '2005-03-02' and '2005-03-02' group by unregTime) as b先写个试试
,三天后注销人数=(select count(*) from tablename
where unregTime>=dateadd(day,3,a.UnregTime))
,三天内注销人数=(select count(*) from tablename
where unregTime<=dateadd(day,3,a.UnregTime))
from tablename
where regTime='2005-03-02'
,三天后注销人数=sum(case when datediff(dd,'2005-1-1',unregtime)>3 then 1 else 0 end)
,三天内注销人数=sum(case when datediff(dd,'2005-1-1',unregtime)<=3 then 1 else 0 end)
from 表
(select count(unregTime) as unregc from 表 where unregTime between '2005-01-01' and '2005-01-04' ) as b
我的改改结果也是对的了
unregcount=sum(case when unregtime between '2005-01-03' and '2005-01-06' then 1 else 0 end)
对了,如果要查询 一个时间段内 (譬如2005-03-02 -2005-03-05)的每天的统计数据(统计方法不变),怎么办Declare @regTime_Begin as char(10)
Declare @regTime_End as char(10)
set @regTime_Begin='2005-03-02'
set @regTime_End='2005-03-02'
while @regTime_Begin<=@regTime_End
Begin
select
sum(case when regTime=@regTime then 1 else 0 end) as 注册人数,
sum(case when regtime between @regTime and dateadd(day,3,@regTime) then 1 else 0 end) 3天内注销人数
from regTable
select @regTime_Begin=dateadd(day,1,@regTime_Begin)
end