select Log_Time1=(select Log_Time from tb where status=0 and convert(varchar(10),Log_Time)=convert(varchar(10),a.Log_Time)),
Log_Time2=(select Log_Time from tb where status=1 and convert(varchar(10),Log_Time)=convert(varchar(10),a.Log_Time))
from tb
group by convert(varchar(10),a.Log_Time)
Log_Time2=(select Log_Time from tb where status=1 and convert(varchar(10),Log_Time)=convert(varchar(10),a.Log_Time))
from tb
group by convert(varchar(10),a.Log_Time)
Log_Time2=(select Log_Time from tb where status=1 and convert(varchar(10),Log_Time)=convert(varchar(10),a.Log_Time))
from tb a
group by convert(varchar(10),a.Log_Time)
from (select * from tb where status='0')a
inner join (select * from tb where status='3')b
on convert(varchar(13,a.Log_Time,120)=convert(varchar(13,b.Log_Time,120)
select Log_Time1=a.Log_Time,Log_Time2=b.Log_Time
from (select * from tb where status='0')a
inner join (select * from tb where status='3')b
on convert(varchar(13),a.Log_Time,120)=convert(varchar(13),b.Log_Time,120)
insert into @t select '2004-12-26 17:32:38.000',0
insert into @t select '2004-12-26 17:34:37.000',3
insert into @t select '2004-12-27 13:27:26.000',0
insert into @t select '2004-12-27 13:29:27.000',3
insert into @t select '2005-01-05 14:11:40.000',0
insert into @t select '2005-01-05 14:15:43.000',3
insert into @t select '2005-01-07 10:35:14.000',0
insert into @t select '2005-01-07 11:19:32.000',3
insert into @t select '2005-01-08 09:42:51.000',0
insert into @t select '2005-01-08 09:44:47.000',3
insert into @t select '2005-01-08 17:37:37.000',0
insert into @t select '2005-01-08 17:40:44.000',3
insert into @t select '2005-01-12 08:48:02.000',0
insert into @t select '2005-01-12 08:52:55.000',3
insert into @t select '2005-01-12 18:06:11.000',0
insert into @t select '2005-01-12 18:14:46.000',3
insert into @t select '2005-01-15 20:38:11.000',0
insert into @t select '2005-01-15 20:40:25.000',3
insert into @t select '2005-01-15 20:44:11.000',0
insert into @t select '2005-01-16 01:40:25.000',3select
Log_Time1=a.Log_Time,
Log_Time2=min(b.Log_Time)
from
(select Log_Time from @t where status=0) a,
(select Log_Time from @t where status=3) b
where
a.Log_Time<b.Log_Time
group by
a.Log_Time/*
Log_Time1 Log_Time2
------------------------ ------------------------
2004-12-26 17:32:38.000 2004-12-26 17:34:37.000
2004-12-27 13:27:26.000 2004-12-27 13:29:27.000
2005-01-05 14:11:40.000 2005-01-05 14:15:43.000
2005-01-07 10:35:14.000 2005-01-07 11:19:32.000
2005-01-08 09:42:51.000 2005-01-08 09:44:47.000
2005-01-08 17:37:37.000 2005-01-08 17:40:44.000
2005-01-12 08:48:02.000 2005-01-12 08:52:55.000
2005-01-12 18:06:11.000 2005-01-12 18:14:46.000
2005-01-15 20:38:11.000 2005-01-15 20:40:25.000
2005-01-15 20:44:11.000 2005-01-16 01:40:25.000
*/
select T0.Log_Time,T3.Log_Time
from
(select (select count(*) from @t where status=T.status and Log_Time<=T.Log_Time) as id ,T.* from @t T where T.status=0) T0,
(select (select count(*) from @t where status=T.status and Log_Time<=T.Log_Time) as id ,T.* from @t T where T.status=3) T3
where T0.id=T3.id
select Log_Time1,Log_Time2
from
(select (select count(*) from t_Log where status=T.status and Log_Time<=T.Log_Time) as id ,Log_Time Log_Time1 from t_Log T where T.status=0) T0,
(select (select count(*) from t_Log where status=T.status and Log_Time<=T.Log_Time) as id ,Log_Time Log_Time2 from t_Log T where T.status=3) T3
where T0.id=T3.id