左联接出现重复记录时取时间最小的那条记录匹配的SQL

左连接出现重复记录时取时间最小的那条记录匹配的SQL

SQL code

select p.rackingstarttime intoTime,s.partcode,s.partname,s.factory,s.house,s.mlotno,s.vendorlot,pd.stockinqty intoQty,s.revisionno,'入库' intoType,to_char(U.Fullname) opearterName,null orderNo,s.orderno sapOrderNo,s.reference sapreference,s.movementtype,wm.remarkfrom Pickingdetail pdjoin Picking p on p.pickingid=pd.pickingidleft join sapmlot s on s.sapmlotid=pd.sapmlotidleft join "USER" U on U.Userid=pd.rackerleft join wminventory wm on wm.sapmlotid=pd.sapmlotid [color=#FF0000]and wm.createdtime=min(wm.createdtime)[/color]Where (pd.state=6)

上面and wm.createdtime=min(wm.createdtime)错误， left join wminventory会出现重复的记录，现在需要把wminventory表中去时间最小的哪条数据用来左连接，请问怎么做改动最小，谢谢。

[解决办法]

SQL code

left join (select row_number() over(partition by sapmlotid order by createdtime) rn,sapmlotid,remark    from wminventory) wm on wm.sapmlotid=pd.sapmlotid and wm.rn=1 

 

查看更多 下一篇