大家帮看一个SQL问题,在线等~ 多谢

tss1011 2009-12-24 02:27:40
目前数据如下所视,


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最新的行)



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

...全文
107 6 打赏 收藏 转发到动态 举报
写回复
用AI写文章
6 条回复
切换为时间正序
请发表友善的回复…
发表回复
dawugui 2009-12-24
  • 打赏
  • 举报
回复
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 行)

*/
dawugui 2009-12-24
  • 打赏
  • 举报
回复
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)
ACMAIN_CHM 2009-12-24
  • 打赏
  • 举报
回复
select *
from table1 a
where not exists (select 1
from table1 where MATERIALCODE=a.MATERIALCODE and BATCHNO=a.BATCHNO and UPLOADDATE>a.UPLOADDATE)
zhangjiang264 2009-12-24
  • 打赏
  • 举报
回复
select * from tb a
where not exists
(
select * from tb b
where a.MATERIALCODE=b.MATERIALCODE and a.BATCHNO=b.BATCHNO and b.UPLOADDATE<a.UPLOADDATE
)

jwdream2008 2009-12-24
  • 打赏
  • 举报
回复
select * from Table1 T where not exists (select * from Table1 where T.MATERIALCODE=MATERIALCODE 
And T.BATCHNO=BATCHNO
And T.UPLOADDATE<UPLOADDATE)
chuifengde 2009-12-24
  • 打赏
  • 举报
回复
select * from [Table] a where not exists(select 1 from [Table] where MATERIALCODE=a.MATERIALCODE and BATCHNO=a.BATCHNO and UPLOADDATE>a.UPLOADDATE)

34,575

社区成员

发帖
与我相关
我的任务
社区描述
MS-SQL Server相关内容讨论专区
社区管理员
  • 基础类社区
  • 二月十六
  • 卖水果的net
加入社区
  • 近7日
  • 近30日
  • 至今
社区公告
暂无公告

试试用AI创作助手写篇文章吧