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 datawith 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]
select case UserName when 'guest' then MAC else userrname end username from [历史记录表] where [DateTime] >DATEADD(dd,-28,getdate()) 要按账号汇总的话,把上面的语句做一个子查询去汇总
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 datawith 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]
要按账号汇总的话,把上面的语句做一个子查询去汇总