select * from (select *,ROW_NUMBER() over(partition by [集装箱号] order by [循环开始时间] desc) as rn from 表) t where t.rn=1
如果你要取最新的2个,就修改t.rn=2
参照@dotnetstudio 增加了结束日期不为NULL,create table test (id nvarchar(10),srq datetime,erq datetime) insert into test select 'SOC001','2012-01-01','2013-01-01' union all select 'SOC001','2013-01-02','2014-01-01' union all select 'SOC001','2014-01-02',null union all select 'SOC002','2013-03-01','2014-02-01' union all select 'SOC002','2014-02-02',null union all select 'SOC003','2014-01-04',null select * from ( select *, ROW_NUMBER() over(partition by id order by erq desc) as rn from test ) t where t.rn<=2 and t.erq is not null
select * from 表 where convert(int,datename(year,循环开始时间)) >= convert(int,datename(year,getdate()))-最新的圈数
如果不需要今年循环没结束的则可以这样:select * from test where convert(int,datename(year,循环开始时间)) between convert(int,datename(year,getdate()))-最新的圈数 and convert(int,datename(year,getdate()))-1
select * from (select *,ROW_NUMBER() over(partition by [集装箱号] order by [循环开始时间] desc) as rn from 表) t
where t.rn=1
insert into test
select 'SOC001','2012-01-01','2013-01-01' union all
select 'SOC001','2013-01-02','2014-01-01' union all
select 'SOC001','2014-01-02',null union all
select 'SOC002','2013-03-01','2014-02-01' union all
select 'SOC002','2014-02-02',null union all
select 'SOC003','2014-01-04',null select *
from (
select *,
ROW_NUMBER() over(partition by id order by erq desc) as rn
from test
) t
where t.rn<=2 and t.erq is not null
select * from 表
where convert(int,datename(year,循环开始时间)) >= convert(int,datename(year,getdate()))-最新的圈数
where convert(int,datename(year,循环开始时间)) between convert(int,datename(year,getdate()))-最新的圈数 and convert(int,datename(year,getdate()))-1