查询当前用户一段时间内的推荐人数

[_VINCE_]

用户表内有 字段。ID、用户ID、 推荐人ID、注册时间 ；需要查询某个用户注册成功后，30天内推荐的人数。时间怎么判断？
select count(ID) from 用户表 where 推荐人ID=某用户 and 注册时间

[Tiger_Zhao]

select count(b.ID) 
  from (SELECT 用户ID, 注册时间
          FROM 用户表
         WHERE where 用户ID=某用户
       ) a
  JOIN 用户表 b
    ON b.推荐人ID = a.用户ID
   AND DateDiff(day, a.注册时间, b.注册时间) <= 30

[开着拖拉机泡妞]

if OBJECT_ID('tb') is not null
drop table tb
go
create table tb
(
	ID int identity,
	userid varchar(10),
	rid varchar(10),
	rdt datetime
)
go
insert tb(userid,rid,rdt)
select '2014001',null,'2014-01-01' union all
select '2014002','2014001','2014-01-08' union all
select '2014003','2014001','2014-01-10' union all
select '2014004','2014001','2014-01-28' union all
select '2014005','2014002','2014-01-01' union all
select '2014006','2014002','2014-01-01' union all
select '2014007',null,'2014-01-01' union all
select '2014008',null,'2014-01-01' union all
select '2014009','2014002','2014-01-01' union all
select '2014010','2014001','2014-02-19' union all
select '2014011','2014004','2014-01-01' union all
select '2014012','2014004','2014-01-01' union all
select '2014013','2014004','2014-01-01'
go

--查询2014001注册成功后推荐的30日内的用户数量

select '2014001' as userid,COUNT(1) as rcounts from tb 
where rid='2014001' and rdt between
(select rdt from tb where userid='2014001')--注册成功时间
and DATEADD(DD,30,(select rdt from tb where userid='2014001'))--注册成功30天后的时间

drop table tb