求一个统计SQL语句

[行云边]

一个用户表里，有 regTime (注册时间),unregTime(注销时间)字段，如果要查询该表里 的某一天（譬如2005-03-02）的注册人数，和该天（指2005-03-02）的3天后的注销人数。请问如何使用一SQL句语来表达？50分全部给写的最好高手 ：）

[iamyuqing]

谢谢大家，
对了，如果要查询 一个时间段内 （譬如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

[行云边]

不好意思 表达有点错误，

和该天（指2005-03-02）的3天后的注销人数 ->
和该天（指2005-03-02）后的3天内的注销人数

[zheninchangjiang]

恭喜某位高手:)

[jinjazz]

--建立测试环境
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 表

[xiaomeixiang]

declare @date datetime
select @date='2005-03-02'

select * from yourtable where convert(varchar(10),regTime,111)=@date and unregTime between @date and @date+3

[xiaomeixiang]

declare @date datetime
select @date='2005-03-02'

select * from yourtable where convert(varchar(10),regTime,111)=@date and unregTime between @date and @date+3

[chinaandys]

select 注册人数=count(*)
,三天后注销人数=(select count(*) from tablename
where unregTime>=dateadd(day,3,a.UnregTime))
,三天内注销人数=(select count(*) from tablename
where unregTime<=dateadd(day,3,a.UnregTime))
from tablename

[iamyuqing]

declare @regTime as char(10)
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

[xiaomeixiang]

declare @date datetime
select @date='2005-03-02'

select count(*) from yourtable where convert(varchar(10),regTime,111)=@date and unregTime between @date and @date+3

[jinjazz]

<=3

[aiur2000]

select regc,unregc from (select count(regTime) as regc from 用户表 where regTime='2005-03-02' group by regtime) as a,
,(select count(unregTime) as unregc from 用户表 where unregTime between '2005-03-02' and '2005-03-02' group by unregTime) as b

先写个试试

[paoluo]

表中应该还有一个用户ID字段吧。

Select (Select SUM(ID) from 用户表 Where regTime=A.regTime) As 注册人数,(Select SUM(ID) from 用户表 Where unregTime=DateAdd(d,3,A.regTime)) As 注销人数 from 用户表 A

[chinaandys]

select 注册人数=(select count(*) from tablename where regTime=a.regTime)
,三天后注销人数=(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'

[aiur2000]

jinjazz写的好啊

[chinaandys]

修改jinjazz(近身剪*10年磨一贴)兄弟的:

select regcount=sum(case when datediff(dd,regtime,'2005-1-1')=0 then 1 else 0 end)
,三天后注销人数=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 表

[aiur2000]

select regc,unregc from (select count(regTime) as regc from 表 where regTime='2005-01-01') as a,
(select count(unregTime) as unregc from 表 where unregTime between '2005-01-01' and '2005-01-04' ) as b


我的改改结果也是对的了

[aiur2000]

case when 条件 then 结果1 else 结果2 end

case when 条件 then 结果1 else 结果2 end

case when 条件 then 结果1 else 结果2 end

一定要学会啊，学习学习

[行云边]

谢谢大家，
对了，如果要查询 一个时间段内 （譬如2005-03-02 -2005-03-05）的每天的统计数据（统计方法不变），怎么办

[jinjazz]

select regcount=sum(case when datediff(dd,regtime,'2005-1-1')=0 then 1 else 0 end),
unregcount=sum(case when unregtime between '2005-01-03' and '2005-01-06' then 1 else 0 end)

[行云边]

呵呵 增加了30 分 谢谢大家