日期:2014-05-16 浏览次数:20433 次
select UserName,
MAX(DateTime)
from 表名
where DateTime>DATEADD(WEEK,-4,GETDATE())
group by UserName
union all
select MAC,
MAX(DateTime)
from 表名
where DateTime>DATEADD(WEEK,-4,GETDATE())
group by MAC
create table TB1(
id int identity(1,1),
username varchar(30) not null,
MAC varchar(30) not null,
[datetime] datetime not null
)
--insert data
with Temp as(
select ROW_NUMBER() over (PARTITION by MAC order by [datetime]) as ID_X,
*
from
TB1 where [datetime]>=dateadd(dd,-28,getdate())
)
--用CTE先把数据整理下,至于然后想要什么数据就简单了,直接在select后面加where条件,想怎么筛就怎么筛
--select * from Temp --活跃账号明细
--select mac,max(id_x) from Temp group by mac--活跃账号四周内登录次数
--select count(distinct mac) from Temp --四周内共有多少活跃账号
select [MAC],COUNT(1) from [历史记录表] where [DateTime] >DATEADD(dd,-28,getdate()) group by [MAC]