目前数据如下所视,
MATERIALCODE BATCHNO UPLOADDATE CREATEDBY010010 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最新的行)MATERIALCODE BATCHNO UPLOADDATE CREATEDBY010010 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
MATERIALCODE BATCHNO UPLOADDATE CREATEDBY010010 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最新的行)MATERIALCODE BATCHNO UPLOADDATE CREATEDBY010010 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
解决方案 »
- [求助]sql server 2005 的数据库 在完整恢复模式下,做差异备份 默认会截断日志吗?
- 求Sql语句(多表联查)
- 自增字段
- 存储过程中本月时间查询的问题?
- 查询除最后一条外所有数据
- select * from a where b like %(select b from ……)% 怎么写
- 手机的英文怎么写,急快,马上赠分。
- 同一数据库中不同用户的导入与导出?(高手请进,急!!!)
- 哪位仁兄可以提供有关bcp或bulk insert语句的详细用法和局限性的说明--俺会继续加分的
- 从字段值"(2003)新民初字第13号"中取出"13"来排序,如何做到?
- sp_executesql的解释
- 我是新加入用户
And T.BATCHNO=BATCHNO
And T.UPLOADDATE<UPLOADDATE)
where not exists
(
select * from tb b
where a.MATERIALCODE=b.MATERIALCODE and a.BATCHNO=b.BATCHNO and b.UPLOADDATE<a.UPLOADDATE
)
from table1 a
where not exists (select 1
from table1 where MATERIALCODE=a.MATERIALCODE and BATCHNO=a.BATCHNO and UPLOADDATE>a.UPLOADDATE)
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')
goselect t.* from tb t where UPLOADDATE = (select max(UPLOADDATE) from tb where MATERIALCODE = t.MATERIALCODE and BATCHNO = t.BATCHNO) order by t.MATERIALCODE , t.BATCHNOselect 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.BATCHNOdrop 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 行)*/