--2005以上版本 select item,price,sdate,edate from( select *,row_number()over(partition by item order by edate desc)as id from tbl ) a where id=1 --2000版本 select * from tbl a where edate=(select max(edate) from tbl b where a.item=b.item)
if object_id('[TB]') is not null drop table [TB] go create table [TB] (item int,price numeric(11,10),sdate datetime,edate datetime) insert into [TB] select 5930,2.6053800000,'2011-06-30 00:00:00.000','2011-07-12 16:40:55.873' union all select 5930,2.8989900000,'2011-09-17 00:00:00.000','2011-09-23 10:54:09.357' union all select 5930,2.4201600000,'2012-02-26 00:00:00.000','2012-02-20 12:00:40.543' union all select 5936,0.1962000000,'2010-07-01 00:00:00.000','2010-07-14 00:00:00.000' union all select 5936,0.2009400000,'2010-08-25 00:00:00.000','2010-09-21 13:45:53.140' union all select 5936,0.2071600000,'2010-09-26 00:00:00.000','2010-10-26 13:53:33.390' union all select 5936,0.2071630000,'2010-09-26 00:00:00.000','2010-11-04 14:47:27.357' union all select 5936,0.2277860000,'2010-10-21 00:00:00.000','2010-12-10 18:15:51.373' union all select 12200,1.4949600000,'2010-07-01 00:00:00.000','2010-07-14 00:00:00.000' union all select 12200,1.4983400000,'2010-08-25 00:00:00.000','2010-09-21 13:45:53.140' union all select 12200,1.5636100000,'2010-09-26 00:00:00.000','2010-10-26 13:53:33.390' union all select 12200,1.5636050000,'2010-09-26 00:00:00.000','2010-11-04 14:47:27.357'select * from [TB]select distinct B.item,b.price,b.sdate,b.edate from TB A cross apply(select top 1 item,price,sdate,edate from TB where item = A.item order by edate desc) B /* 5930 2.4201600000 2012-02-26 00:00:00.000 2012-02-20 12:00:40.543 5936 0.2277860000 2010-10-21 00:00:00.000 2010-12-10 18:15:51.373 12200 1.5636050000 2010-09-26 00:00:00.000 2010-11-04 14:47:27.357*/
select a.* from tab a inner join (select item,max(edate) maxedate from tab group by item) b on a.item=b.item and a.edate=b.maxedate
selct distinct item,max(edate) from 表 gourp by item
或者: select * from tbl a where not exists( select 1 from tbl b where a.item=b.item and a.edate<b.edate)
select item,price,sdate,edate from(
select *,row_number()over(partition by item order by edate desc)as id from tbl
) a where id=1
--2000版本
select * from tbl a where edate=(select max(edate) from tbl b where a.item=b.item)
go
create table [TB] (item int,price numeric(11,10),sdate datetime,edate datetime)
insert into [TB]
select 5930,2.6053800000,'2011-06-30 00:00:00.000','2011-07-12 16:40:55.873' union all
select 5930,2.8989900000,'2011-09-17 00:00:00.000','2011-09-23 10:54:09.357' union all
select 5930,2.4201600000,'2012-02-26 00:00:00.000','2012-02-20 12:00:40.543' union all
select 5936,0.1962000000,'2010-07-01 00:00:00.000','2010-07-14 00:00:00.000' union all
select 5936,0.2009400000,'2010-08-25 00:00:00.000','2010-09-21 13:45:53.140' union all
select 5936,0.2071600000,'2010-09-26 00:00:00.000','2010-10-26 13:53:33.390' union all
select 5936,0.2071630000,'2010-09-26 00:00:00.000','2010-11-04 14:47:27.357' union all
select 5936,0.2277860000,'2010-10-21 00:00:00.000','2010-12-10 18:15:51.373' union all
select 12200,1.4949600000,'2010-07-01 00:00:00.000','2010-07-14 00:00:00.000' union all
select 12200,1.4983400000,'2010-08-25 00:00:00.000','2010-09-21 13:45:53.140' union all
select 12200,1.5636100000,'2010-09-26 00:00:00.000','2010-10-26 13:53:33.390' union all
select 12200,1.5636050000,'2010-09-26 00:00:00.000','2010-11-04 14:47:27.357'select * from [TB]select distinct B.item,b.price,b.sdate,b.edate
from TB A
cross apply(select top 1 item,price,sdate,edate from TB where item = A.item order by edate desc) B
/*
5930 2.4201600000 2012-02-26 00:00:00.000 2012-02-20 12:00:40.543
5936 0.2277860000 2010-10-21 00:00:00.000 2010-12-10 18:15:51.373
12200 1.5636050000 2010-09-26 00:00:00.000 2010-11-04 14:47:27.357*/
select a.*
from tab a
inner join
(select item,max(edate) maxedate
from tab group by item) b
on a.item=b.item and a.edate=b.maxedate
selct distinct item,max(edate) from 表 gourp by item
或者:
select * from tbl a where not exists(
select 1 from tbl b where a.item=b.item and a.edate<b.edate)