# SQL　统计　某段时间内的活跃帐号数

0

tneduts | 菜鸟二级 | 园豆：239

0

`/*create table bw24417(    [date] datetime,    accountid varchar(20),    logincount int)insert into bw24417 values (GETDATE(),'1',1);insert into bw24417 values (GETDATE(),'3',1);insert into bw24417 values (GETDATE(),'1',1);insert into bw24417 values (GETDATE(),'1',1);insert into bw24417 values (GETDATE(),'2',1);insert into bw24417 values (GETDATE(),'1',1);insert into bw24417 values (GETDATE(),'2',1);insert into bw24417 values (GETDATE(),'4',1);insert into bw24417 values (GETDATE(),'3',1);insert into bw24417 values (GETDATE(),'4',1);insert into bw24417 values (GETDATE(),'3',1);*/select * from bw24417;select accountid,count(logincount) AS huoyue from bw24417 where DATEDIFF(day,[date],GETDATE())<7 group by accountid having count(logincount)>2;结果：date                    accountid            logincount----------------------- -------------------- -----------2011-04-25 11:30:20.167 1                    12011-04-25 11:30:20.190 3                    12011-04-25 11:30:20.190 1                    12011-04-25 11:30:20.190 1                    12011-04-25 11:30:20.200 2                    12011-04-25 11:30:20.200 1                    12011-04-25 11:30:20.200 2                    12011-04-25 11:30:20.200 4                    12011-04-25 11:30:20.200 3                    12011-04-25 11:30:20.200 4                    12011-04-25 11:30:20.200 3                    1(11 row(s) affected)accountid            huoyue-------------------- -----------1                    43                    3(2 row(s) affected)`

artwl | 园豆：16536 (专家六级) | 2011-04-25 11:46
SQL语句中的数字7改为你想要的就OK了啊

--获取活跃用户的数帐号
DECLARE @StartDate DATETIME ,
@EndDate DATETIME

SET @StartDate = '2010-01-04'
SET @EndDate = '2010-01-10' ;
WITH temp
AS ( SELECT A.Date ,
B.UserID ,
iswwt ,
IsRegular
FROM ( SELECT *
FROM dbo.wcfn_GetTimeSpanTable(@StartDate,
@EndDate)
) A ,--时间区间
( SELECT UserID ,
Date ,
IsWWT ,
IsRegular
WHERE Date BETWEEN @StartDate - 6 AND @EndDate
) B--区间内的登录记录
WHERE B.Date BETWEEN A.Date - 6 AND A.Date
GROUP BY A.Date ,
B.UserID ,
b.IsWWT ,
b.IsRegular
HAVING COUNT(B.Date) > 2
)
SELECT date ,
SUM(CASE WHEN ( iswwt = 0
AND IsRegular = 1
AND NOT ( UserID LIKE 'SJZC%' )
AND UserID <> '____anonymous'
AND UserID <> 'null'
) THEN 1
ELSE 0
END) AS [正式机构活跃数] ,
SUM(CASE WHEN ( ( iswwt <> 0
OR iswwt IS NULL
)
AND IsRegular = 1
AND NOT ( UserID LIKE 'SJZC%' )
AND UserID <> '_

0
`selectconvert(varchar(6),date,112),accountid From [table] Group By convert(varchar(6),date,112) Having lodincount>2`

http://www.cnblogs.com/mapeng-11/archive/2011/03/09/1978417.html

0

`declare @days int,@count int,@date datetime set @days=7set @count=3set @date=getdate()select accountid from [loginInfoTable]where datediff(dd,@date,[date])<@daysgroup by accountidhaving sum(logincount)>=@count`

您需要登录以后才能回答，未注册用户请先注册