if object_id('Tempdb..#temp') is not null drop table #temp --建临时表 create table #temp( Id int identity(1,1) not null, userid int null, tableid int null, loginstatus int null, logintime datetime null, quittime datetime null ) --测试用例 Insert #temp select 1,1,1,'2013-06-21 01:00:00','2013-06-21 05:00:00' union all select 2,1,1,'2013-06-21 01:20:00','2013-06-21 01:25:00' union all select 3,1,1,'2013-06-21 01:30:00','2013-06-21 01:40:00' union all select 1,1,1,'2013-06-22 02:00:00','2013-06-22 02:30:00' union all select 2,1,1,'2013-06-22 02:20:00','2013-06-22 02:45:00' union all select 3,1,1,'2013-06-22 02:10:00','2013-06-22 03:20:00' union all select 1,1,1,'2013-06-23 03:00:00','2013-06-23 03:10:00' union all select 2,1,1,'2013-06-23 03:20:00','2013-06-23 04:55:00' union all select 3,1,1,'2013-06-23 03:30:00','2013-06-23 03:40:00'--定义 查询时间、桌子id 变量 declare @querytime datetime declare @tableid intset @querytime='2013-06-22 03:10:00' set @tableid=1--根据你的条件查询 --本例查询 桌子id为1 、时间为 2013-06-22 03:10:00 的登录记录 select * from #temp where tableid=@tableid and (@querytime between logintime and quittime)--查询结果 (9 行受影响) Id userid tableid loginstatus logintime quittime ----------- ----------- ----------- ----------- ----------------------- ----------------------- 6 3 1 1 2013-06-22 02:10:00.000 2013-06-22 03:20:00.000
select * from tablename t where dtime=(select max(dtime) from tablename where userid=t.userid) and dtime between 开始时间 and 结束时间
--查最后一条登录数据 select top 1 * from #temp where tableid=@tableid and (@querytime between logintime and quittime) order by 1 desc
select t from t_record t where t.tableId = tableId and t.logoutTime > startTime and t.logoutTime < endTime and not exit (select s from t_record s where s.loginTime >= t.logoutTime and t.tableId = tableId ) 这个是我刚弄出来的。 有没有更优的方案,startTime-endTime 是定义的时间段
--时间段 starttime,endtime --tableid 具体id你自己定 declare @tableid int set @tableid=1---查所有 符合时间段、桌子id条件的记录 select * from t_record where tableid=@tableid and (startTime between loginTime and logoutTime) and (endTime between loginTime and logoutTime)--查最后一个登陆的 select top 1 * from t_record where tableid=@tableid and (startTime between loginTime and logoutTime) and (endTime between loginTime and logoutTime) order by loginTime desc
你讲一堆中文逻辑,别人怎么写语句给你?
--建临时表
create table #temp(
Id int identity(1,1) not null,
userid int null,
tableid int null,
loginstatus int null,
logintime datetime null,
quittime datetime null
)
--测试用例
Insert #temp
select 1,1,1,'2013-06-21 01:00:00','2013-06-21 05:00:00' union all
select 2,1,1,'2013-06-21 01:20:00','2013-06-21 01:25:00' union all
select 3,1,1,'2013-06-21 01:30:00','2013-06-21 01:40:00' union all
select 1,1,1,'2013-06-22 02:00:00','2013-06-22 02:30:00' union all
select 2,1,1,'2013-06-22 02:20:00','2013-06-22 02:45:00' union all
select 3,1,1,'2013-06-22 02:10:00','2013-06-22 03:20:00' union all
select 1,1,1,'2013-06-23 03:00:00','2013-06-23 03:10:00' union all
select 2,1,1,'2013-06-23 03:20:00','2013-06-23 04:55:00' union all
select 3,1,1,'2013-06-23 03:30:00','2013-06-23 03:40:00'--定义 查询时间、桌子id 变量
declare @querytime datetime
declare @tableid intset @querytime='2013-06-22 03:10:00'
set @tableid=1--根据你的条件查询
--本例查询 桌子id为1 、时间为 2013-06-22 03:10:00 的登录记录
select * from #temp
where tableid=@tableid and (@querytime between logintime and quittime)--查询结果
(9 行受影响)
Id userid tableid loginstatus logintime quittime
----------- ----------- ----------- ----------- ----------------------- -----------------------
6 3 1 1 2013-06-22 02:10:00.000 2013-06-22 03:20:00.000
where dtime=(select max(dtime) from tablename where userid=t.userid)
and dtime between 开始时间 and 结束时间
--查最后一条登录数据
select top 1 * from #temp
where tableid=@tableid and (@querytime between logintime and quittime)
order by 1 desc
where t.tableId = tableId and
t.logoutTime > startTime and
t.logoutTime < endTime and
not exit (select s from t_record s where s.loginTime >= t.logoutTime and t.tableId = tableId ) 这个是我刚弄出来的。 有没有更优的方案,startTime-endTime 是定义的时间段
--时间段 starttime,endtime
--tableid 具体id你自己定
declare @tableid int
set @tableid=1---查所有 符合时间段、桌子id条件的记录
select * from t_record
where tableid=@tableid
and (startTime between loginTime and logoutTime)
and (endTime between loginTime and logoutTime)--查最后一个登陆的
select top 1 * from t_record
where tableid=@tableid
and (startTime between loginTime and logoutTime)
and (endTime between loginTime and logoutTime)
order by loginTime desc