代码
declare
@t
table
(id
int
, status
char
(
1
), UpdateDate
datetime
,
primary
key
(id,updatedate))
insert into @t
select 1 , ' A ' , ' 2010-2-1 ' UNION ALL
select 2 , ' B ' , ' 2010-2-3 ' UNION ALL
select 1 , ' A ' , ' 2010-2-4 ' UNION ALL
select 2 , ' C ' , ' 2010-2-5 ' UNION ALL
select 1 , ' A ' , ' 2010-2-7 ' UNION ALL
select 3 , ' B ' , ' 2010-2-8 ' UNION ALL
select 1 , ' B ' , ' 2010-2-9 ' union all
select 2 , ' B ' , ' 2010-2-10 ' union all
select 3 , ' B ' , ' 2010-2-11 '
SELECT * FROM @T
SELECT id,Status,updatedate
FROM @T a
where exists ( select * from @t where a.id = id and a.status <> status and updatedate = ( select max (updatedate) from @t where id = a.id and updatedate < a.updatedate))
or exists ( select * from @t where a.id = id and a.status <> status and updatedate = ( select min (updatedate) from @t where id = a.id and updatedate > a.updatedate))
insert into @t
select 1 , ' A ' , ' 2010-2-1 ' UNION ALL
select 2 , ' B ' , ' 2010-2-3 ' UNION ALL
select 1 , ' A ' , ' 2010-2-4 ' UNION ALL
select 2 , ' C ' , ' 2010-2-5 ' UNION ALL
select 1 , ' A ' , ' 2010-2-7 ' UNION ALL
select 3 , ' B ' , ' 2010-2-8 ' UNION ALL
select 1 , ' B ' , ' 2010-2-9 ' union all
select 2 , ' B ' , ' 2010-2-10 ' union all
select 3 , ' B ' , ' 2010-2-11 '
SELECT * FROM @T
SELECT id,Status,updatedate
FROM @T a
where exists ( select * from @t where a.id = id and a.status <> status and updatedate = ( select max (updatedate) from @t where id = a.id and updatedate < a.updatedate))
or exists ( select * from @t where a.id = id and a.status <> status and updatedate = ( select min (updatedate) from @t where id = a.id and updatedate > a.updatedate))