34,575
社区成员
发帖
与我相关
我的任务
分享
MATERIALCODE BATCHNO UPLOADDATE CREATEDBY
010010 0800028616 12/2/2009 5:57:37 PM RO2
MP20240E 0900031303 11/16/2009 3:38:03 PM RO2
030072 0900031310 11/17/2009 1:05:23 PM RO2
030072 0900031310 11/17/2009 5:39:36 PM ts1
010235 0900031311 11/17/2009 7:04:24 PM RO2
010235 0900031311 11/18/2009 10:02:36 AM ts1
220035 0900031312 11/18/2009 3:41:57 PM ts1
MATERIALCODE BATCHNO UPLOADDATE CREATEDBY
010010 0800028616 12/2/2009 5:57:37 PM RO2
MP20240E 0900031303 11/16/2009 3:38:03 PM RO2
030072 0900031310 11/17/2009 5:39:36 PM ts1
010235 0900031311 11/18/2009 10:02:36 AM ts1
220035 0900031312 11/18/2009 3:41:57 PM ts1
create table tb(MATERIALCODE varchar(20), BATCHNO varchar(20), UPLOADDATE datetime, CREATEDBY varchar(20))
insert into tb values('010010' , '0800028616' , '12/2/2009 5:57:37 PM' , 'RO2')
insert into tb values('MP20240E', '0900031303' , '11/16/2009 3:38:03 PM' , 'RO2')
insert into tb values('030072' , '0900031310' , '11/17/2009 1:05:23 PM' , 'RO2')
insert into tb values('030072' , '0900031310' , '11/17/2009 5:39:36 PM' , 'ts1')
insert into tb values('010235' , '0900031311' , '11/17/2009 7:04:24 PM' , 'RO2')
insert into tb values('010235' , '0900031311' , '11/18/2009 10:02:36 AM', 'ts1')
insert into tb values('220035' , '0900031312' , '11/18/2009 3:41:57 PM' , 'ts1')
go
select t.* from tb t where UPLOADDATE = (select max(UPLOADDATE) from tb where MATERIALCODE = t.MATERIALCODE and BATCHNO = t.BATCHNO) order by t.MATERIALCODE , t.BATCHNO
select t.* from tb t where not exists (select 1 from tb where MATERIALCODE = t.MATERIALCODE and BATCHNO = t.BATCHNO and UPLOADDATE > t.UPLOADDATE) order by t.MATERIALCODE , t.BATCHNO
drop table tb
/*
MATERIALCODE BATCHNO UPLOADDATE CREATEDBY
-------------------- -------------------- ------------------------------------------------------ --------------------
010010 0800028616 2009-12-02 17:57:37.000 RO2
010235 0900031311 2009-11-18 10:02:36.000 ts1
030072 0900031310 2009-11-17 17:39:36.000 ts1
220035 0900031312 2009-11-18 15:41:57.000 ts1
MP20240E 0900031303 2009-11-16 15:38:03.000 RO2
(所影响的行数为 5 行)
*/
select t.* from tb t where UPLOADDATE = (select max(UPLOADDATE) from tb where MATERIALCODE = t.MATERIALCODE and BATCHNO = t.BATCHNO)
select t.* from tb t where not exists (select 1 from tb where MATERIALCODE = t.MATERIALCODE and BATCHNO = t.BATCHNO and UPLOADDATE > t.UPLOADDATE)
select *
from table1 a
where not exists (select 1
from table1 where MATERIALCODE=a.MATERIALCODE and BATCHNO=a.BATCHNO and UPLOADDATE>a.UPLOADDATE)
select * from Table1 T where not exists (select * from Table1 where T.MATERIALCODE=MATERIALCODE
And T.BATCHNO=BATCHNO
And T.UPLOADDATE<UPLOADDATE)
select * from [Table] a where not exists(select 1 from [Table] where MATERIALCODE=a.MATERIALCODE and BATCHNO=a.BATCHNO and UPLOADDATE>a.UPLOADDATE)