create table tb(agent_code varchar(20), start_Time datetime, status varchar(10) )
Insert into tb
select 'wh0001','2004-11-18 14:42:21','登錄'
union all select 'wh0001','2004-11-18 14:53:39','退出'
union all select 'wh0001','2004-11-18 15:42:21','登錄'
union all select 'wh0001','2004-11-18 15:53:39','退出'
union all select 'wh0001','2004-11-18 16:42:21','登錄'
union all select 'wh0001','2004-11-18 16:53:39','退出'
union all select 'wh0001','2004-11-18 17:42:21','登錄'
union all select 'wh0001','2004-11-18 17:53:39','退出'select * from tbselect a.* ,EndTime=(select min(start_Time) from tb where start_Time>a.start_Time),status2='退出'
from tb a
where status='登錄'--結果
agent_code start_Time status EndTime status2
-----------------------------------------------------------------------------------------------
wh0001 2004-11-18 14:42:21.000 登錄 2004-11-18 14:53:39.000 退出
wh0001 2004-11-18 15:42:21.000 登錄 2004-11-18 15:53:39.000 退出
wh0001 2004-11-18 16:42:21.000 登錄 2004-11-18 16:53:39.000 退出
wh0001 2004-11-18 17:42:21.000 登錄 2004-11-18 17:53:39.000 退出
from tb a
where status='登錄'答案也和上面一樣
select 'wh0001', '2004-11-18 14:42:21', '登录'
union all select 'wh0001', '2004-11-18 14:53:39', '退出'
union all select 'wh0001', '2004-11-18 15:42:21', '登录'
union all select 'wh0001', '2004-11-18 15:53:39', '退出'
union all select 'wh0001', '2004-11-18 16:42:21', '登录'
union all select 'wh0001', '2004-11-18 16:53:39', '退出'
union all select 'wh0001', '2004-11-18 17:42:21', '登录'
union all select 'wh0001', '2004-11-18 17:53:39', '退出'
-----------------------------
select a.agent_code,a.start_time,a.status,min(b.start_time) as end_time,b.status as end_status from Tss_Agent_Log as a,Tss_Agent_Log as b
where a.agent_code=b.agent_code and a.status='登录' and b.status='退出' and a.start_time<b.start_time group by a.agent_code,a.start_time,a.status,b.status
--------------------------------------------------
agent_code start_time status end_time end_status
-------------------- ------------------------------------------------------ ---------- ------------------------------------------------------ ----------
wh0001 2004-11-18 14:42:21.000 登录 2004-11-18 14:53:39.000 退出
wh0001 2004-11-18 15:42:21.000 登录 2004-11-18 15:53:39.000 退出
wh0001 2004-11-18 16:42:21.000 登录 2004-11-18 16:53:39.000 退出
wh0001 2004-11-18 17:42:21.000 登录 2004-11-18 17:53:39.000 退出(所影响的行数为 4 行)