临时表中有三列
number state time
1 1 2011-2-7 8:00
1 2 2011-2-7 9:00
2 1 2011-2-7 10:00
2 2 2011-2-7 11:00
3 1 2011-2-7 12:00
3 2 2011-2-7 11:00
4 1 2011-2-7 16:00
4 2 2011-2-7 12:00相同 number 的,根据state状态不同,进行时间的比较,取出state=1的条件下时间较大的数据帮忙看下谢谢!
number state time
1 1 2011-2-7 8:00
1 2 2011-2-7 9:00
2 1 2011-2-7 10:00
2 2 2011-2-7 11:00
3 1 2011-2-7 12:00
3 2 2011-2-7 11:00
4 1 2011-2-7 16:00
4 2 2011-2-7 12:00相同 number 的,根据state状态不同,进行时间的比较,取出state=1的条件下时间较大的数据帮忙看下谢谢!
declare @T table (number int,state int,time datetime)
insert into @T
select 1,1,'2011-2-7 8:00' union all
select 1,2,'2011-2-7 9:00' union all
select 2,1,'2011-2-7 10:00' union all
select 2,2,'2011-2-7 11:00' union all
select 3,1,'2011-2-7 12:00' union all
select 3,2,'2011-2-7 11:00' union all
select 4,1,'2011-2-7 16:00' union all
select 4,2,'2011-2-7 12:00'select * from @T t where
time=(select max(time) from @T where number=t.number) and state=1/*
number state time
----------- ----------- -----------------------
3 1 2011-02-07 12:00:00.000
4 1 2011-02-07 16:00:00.000
*/
where not exists(select 1 from t1 where a.state=state and a.time<time)
and a.state=1
select number , state , time from
(
select number , state , time,row_number() over(partition by number order by time desc) as pg from tab where state=1
)t where pg=1
--number state time
select *
from tb t
where not exists (select 1 from tb where number = t.number and state = 1 and time > t.time)
and state = 1
insert into @t
select 1 ,1 ,'2011-2-7 8:00' union
select 1 ,2 ,'2011-2-7 9:00' union
select 2 ,1 ,'2011-2-7 10:00' union
select 2 ,2 ,'2011-2-7 11:00' union
select 3 ,1 ,'2011-2-7 12:00' union
select 3 ,2 ,'2011-2-7 11:00' union
select 4 ,1 ,'2011-2-7 16:00' union
select 4 ,2 ,'2011-2-7 12:00';with t
as(
select ID=row_number() over(partition by number order by time1 desc ) ,number,state1,time1 from @t)
select number,state1,time1 from t where ID=1 and state1=1
3 1 2011-02-07 12:00:00.000
4 1 2011-02-07 16:00:00.000
create table #t
(number int, states int, times datetime)insert into #t
select 1, 1, '2011-2-7 8:00' union all
select 1, 2, '2011-2-7 9:00' union all
select 2, 1, '2011-2-7 10:00' union all
select 2, 2, '2011-2-7 11:00' union all
select 3, 1, '2011-2-7 12:00' union all
select 3, 2, '2011-2-7 11:00' union all
select 4, 1, '2011-2-7 16:00' union all
select 4, 2, '2011-2-7 12:00'
select a.*
from #t a
inner join
(select number,max(times) maxtimes
from #t where states=1
group by number) b
on a.number=b.number
and a.times=b.maxtimesnumber states times
----------- ----------- -----------------------
1 1 2011-02-07 08:00:00.000
2 1 2011-02-07 10:00:00.000
3 1 2011-02-07 12:00:00.000
4 1 2011-02-07 16:00:00.000(4 row(s) affected)