上面搞错了,不好意思select a.* from talbe a where a.createdate=(select max(createdate)from table where barcode=a.barcode) order by id
select a.* from [表名] a where not exists(select 1 from [表名] b where b.barcode=a.barcode and b.createdate>a.createdate)
select * from a where createtime=(select max(createtime) from a group by barcode )
SELECT tb1.* FROM tb1 INNER JOIN (SELECT ID,barcode,PATH,MAX(createdate) AS createdate from tb1 GROUP BY ID,barcode,path ) tb2 ON tb1.ID=tb2.ID AND tb1.barcode=tb2.barcode AND tb1.path=tb2.path AND tb1.createdate=rb2.createdate
if OBJECT_ID('tab1') is not null drop table tab1 go create table tab1 ( id int primary key identity(1,1), barcode nvarchar(20), createdate date, path nvarchar(20) ) insert into tab1 select 'barcode1','2014-08-05','无' union all select 'barcode1','2014-08-10','无' union all select 'barcode2','2014-08-07','无' union all select 'barcode3','2014-08-09','无' --1 select * from ( select ROW_NUMBER() over( partition by barcode order by createdate desc) as d,* from tab1 )x where d =1 --2 select a.* from tab1 a where not exists(select 1 from tab1 b where b.barcode=a.barcode and b.createdate>a.createdate)
select * from a as a1 where createtime=(select max(createtime) from a as a2 where a1.barcode=a2.barcode )
create table t ( ID int, barcode varchar(20), createdate varchar(10), path varchar(100) )insert into t values(1,'aaa','2011-01-01','a111111111111') insert into t values(2,'aaa','2012-01-01','a22222222222222') insert into t values(3,'bbb','2011-01-01','a3333333333333333333') insert into t values(4,'ccc','2011-01-01','a444444444444444') select t.* from t , (select barcode,MAX(createdate) createdate from t group by barcode )b where t.barcode=b.barcode and t.createdate=b.createdate order by id
上面搞错了,不好意思select a.* from talbe a
where a.createdate=(select max(createdate)from table
where barcode=a.barcode)
order by id
select a.*
from [表名] a
where not exists(select 1
from [表名] b
where b.barcode=a.barcode
and b.createdate>a.createdate)
group by barcode )
SELECT tb1.* FROM tb1
INNER JOIN (SELECT ID,barcode,PATH,MAX(createdate) AS createdate from tb1 GROUP BY ID,barcode,path ) tb2
ON tb1.ID=tb2.ID AND tb1.barcode=tb2.barcode AND tb1.path=tb2.path AND tb1.createdate=rb2.createdate
if OBJECT_ID('tab1') is not null
drop table tab1
go
create table tab1
(
id int primary key identity(1,1),
barcode nvarchar(20),
createdate date,
path nvarchar(20)
)
insert into tab1
select 'barcode1','2014-08-05','无' union all
select 'barcode1','2014-08-10','无' union all
select 'barcode2','2014-08-07','无' union all
select 'barcode3','2014-08-09','无'
--1
select * from
(
select ROW_NUMBER() over( partition by barcode order by createdate desc) as d,* from tab1
)x
where d =1
--2
select a.* from tab1 a
where not exists(select 1 from tab1 b where b.barcode=a.barcode and b.createdate>a.createdate)
where a1.barcode=a2.barcode )
(
ID int,
barcode varchar(20),
createdate varchar(10),
path varchar(100)
)insert into t values(1,'aaa','2011-01-01','a111111111111')
insert into t values(2,'aaa','2012-01-01','a22222222222222')
insert into t values(3,'bbb','2011-01-01','a3333333333333333333')
insert into t values(4,'ccc','2011-01-01','a444444444444444')
select t.* from t ,
(select barcode,MAX(createdate) createdate from t group by barcode )b where t.barcode=b.barcode
and t.createdate=b.createdate order by id