试试这个。select
l1.id,
l1.newstate as state,
l1.time as start_time,
(select
min(time)
from
log l2
where
l2.newstate = 2 and l2.time > l1.time
) as end_time
from
log l1
where
l1.newstate = 6
l1.id,
l1.newstate as state,
l1.time as start_time,
(select
min(time)
from
log l2
where
l2.newstate = 2 and l2.time > l1.time
) as end_time
from
log l1
where
l1.newstate = 6
l1.id,
l1.newstate as state,
l1.time as start_time,
(select
min(l2.time)
from
log l2
where
l2.type = 2 and l2.time > l1.time
) as end_time
from
log l1
where
l1.newstate = 6
不过大概意思就是这样。select
l1.id,
l1.newstate as state,
l1.time as start_time,
(select
min(l2.time)
from
log l2
where
l2.type = 2 and l2.time > l1.time
) as end_time
from
log l1
where
l1.newstate in (6, 7)
create table t(
id varchar(3),
type int,
oldstate int,
newstate int,
time datetime)insert into t select '001',4 ,2,6,cast('20041010' as datetime)
insert into t select '001',2 ,6,0,cast('20041011' as datetime)
insert into t select '002',9 ,1,6,cast('20041209' as datetime)
insert into t select '002',10,6,7,cast('20041212' as datetime)
insert into t select '002',2 ,7,0,cast('20041216' as datetime)
insert into t select '002',9 ,1,6,cast('20050109' as datetime)
insert into t select '014',9 ,1,6,cast('20040707' as datetime)
insert into t select '014',10,6,7,cast('20040710' as datetime)
insert into t select '015',9 ,1,6,cast('20040707' as datetime)
insert into t select '021',4 ,2,7,cast('20041028' as datetime)
insert into t select '021',2 ,7,0,cast('20041029' as datetime)
insert into t select '021',9 ,1,6,cast('20041101' as datetime)
insert into t select '021',10,6,7,cast('20041105' as datetime)
insert into t select '021',2 ,7,0,cast('20041201' as datetime)
--生成中间表
select identity(int,1,1) as nid,* into #t from t order by id,time
--利用中间表查询
select
d.id,d.status,d.start_time,max(d.end_time) as end_time
from
(select
a.id,
a.newstate as status,
start_time = a.time,
case when a.nid =(select min(nid) from #t where id = a.id and nid < isnull(b.nid,0) and nid > (select isnull(max(nid),0) from #t where type = 2 and nid < isnull(b.nid,0)))
then b.time else null end as end_time
from
(select * from #t where type != 2 and newstate in(6,7)) a
left join
(select * from #t where type = 2 ) b
on
a.id = b.id) d
group by
d.id,d.status,d.start_time
order by
d.id,d.start_time
--输出查询结果
id status start_time end_time
-----------------------------------------
001 6 2004-10-10 2004-10-11
002 6 2004-12-09 2004-12-16
002 7 2004-12-12 NULL
002 6 2005-01-09 NULL
014 6 2004-07-07 NULL
014 7 2004-07-10 NULL
015 6 2004-07-07 NULL
021 7 2004-10-28 2004-10-29
021 6 2004-11-01 2004-12-01
021 7 2004-11-05 NULL
create table t(
id varchar(3),
type int,
oldstate int,
newstate int,
time datetime)insert into t select '001',4 ,2,6,cast('20041010' as datetime)
insert into t select '001',2 ,6,0,cast('20041011' as datetime)
insert into t select '002',9 ,1,6,cast('20041209' as datetime)
insert into t select '002',10,6,7,cast('20041212' as datetime)
insert into t select '002',2 ,7,0,cast('20041216' as datetime)
insert into t select '002',9 ,1,6,cast('20050109' as datetime)
insert into t select '014',9 ,1,6,cast('20040707' as datetime)
insert into t select '014',10,6,7,cast('20040710' as datetime)
insert into t select '015',9 ,1,6,cast('20040707' as datetime)
insert into t select '021',4 ,2,7,cast('20041028' as datetime)
insert into t select '021',2 ,7,0,cast('20041029' as datetime)
insert into t select '021',9 ,1,6,cast('20041101' as datetime)
insert into t select '021',10,6,7,cast('20041105' as datetime)
insert into t select '021',2 ,7,0,cast('20041201' as datetime)
--生成中间表
select identity(int,1,1) as nid,* into #t from t order by id,time
--利用中间表查询
select
d.id,d.status,d.start_time,max(d.end_time) as end_time
from
(select
a.id,
a.newstate as status,
start_time = a.time,
case when a.nid =(select min(nid) from #t where id = a.id and nid < isnull(b.nid,0) and nid > (select isnull(max(nid),0) from #t where type = 2 and nid < isnull(b.nid,0)))
then b.time else null end as end_time
from
(select * from #t where type != 2 and newstate in(6,7)) a
left join
(select * from #t where type = 2 ) b
on
a.id = b.id) d
group by
d.id,d.status,d.start_time
order by
d.id,d.start_time
--输出查询结果
id status start_time end_time
-----------------------------------------
001 6 2004-10-10 2004-10-11
002 6 2004-12-09 2004-12-16
002 7 2004-12-12 NULL
002 6 2005-01-09 NULL
014 6 2004-07-07 NULL
014 7 2004-07-10 NULL
015 6 2004-07-07 NULL
021 7 2004-10-28 2004-10-29
021 6 2004-11-01 2004-12-01
021 7 2004-11-05 NULL