select * from tb1 a where not exists (select 1 from tb1 where datetime2<datetime1 and datetime1='2005-9-24' and type='确定' and datetime2<a.datetime2) and datetime2<datetime1 and datetime1='2005-9-24' and type='确定'
select * from tb where datetime1 = '2005-9-24' and datetime2 < datetime1 and type ='确定' and datetime2 = (select min (datetime2) from tb1 where datetime1 = '2005-9-24' and datetime2 < datetime1 and type ='确定' )
--测试环境 declare @t table ( P_ID int,NAME varchar(10),TYPE varchar(10),DATETIME1 varchar(10),DATETIME2 varchar(10)) insert into @t select 1,'CESHI','确定','2005-9-23','2005-9-24' union all select 2,'CE','确定','2005-9-23','2005-9-23' union all select 1,'CESHI','解除','2005-9-24','2005-9-24' union all select 1,'CESHI','确定','2005-9-24','2005-9-23' union all select 2,'CE','解除','2005-9-24','2005-9-23' union all select 2,'CE','确定','2005-9-24','2005-9-26' --查询 select * from @t a where DATETIME1='2005-9-24' and datediff(day,convert(datetime,DATETIME2),convert(datetime,DATETIME1))>0 and TYPE='确定' and not exists (select 1 from @t where NAME=a.NAME and DATETIME2<a.DATETIME2) --结果 P_ID NAME TYPE DATETIME1 DATETIME2 ----------- ---------- ---------- ---------- ---------- 1 CESHI 确定 2005-9-24 2005-9-23(所影响的行数为 1 行)
where not exists (select 1 from tb1 where
datetime2<datetime1 and datetime1='2005-9-24' and type='确定' and datetime2<a.datetime2)
and datetime2<datetime1 and datetime1='2005-9-24' and type='确定'
where datetime1 = '2005-9-24'
and datetime2 < datetime1
and type ='确定'
and datetime2 =
(select min (datetime2) from tb1
where datetime1 = '2005-9-24'
and datetime2 < datetime1
and type ='确定'
)
declare @t table ( P_ID int,NAME varchar(10),TYPE varchar(10),DATETIME1 varchar(10),DATETIME2 varchar(10))
insert into @t select 1,'CESHI','确定','2005-9-23','2005-9-24'
union all select 2,'CE','确定','2005-9-23','2005-9-23'
union all select 1,'CESHI','解除','2005-9-24','2005-9-24'
union all select 1,'CESHI','确定','2005-9-24','2005-9-23'
union all select 2,'CE','解除','2005-9-24','2005-9-23'
union all select 2,'CE','确定','2005-9-24','2005-9-26'
--查询
select * from @t a
where DATETIME1='2005-9-24'
and datediff(day,convert(datetime,DATETIME2),convert(datetime,DATETIME1))>0
and TYPE='确定'
and not exists (select 1 from @t where NAME=a.NAME and DATETIME2<a.DATETIME2)
--结果
P_ID NAME TYPE DATETIME1 DATETIME2
----------- ---------- ---------- ---------- ----------
1 CESHI 确定 2005-9-24 2005-9-23(所影响的行数为 1 行)