在number openquery上使用最大日期分组获取成本

Question

我试图从另一台服务器中选择信息，并通过打开的查询将其插入到表中.到目前为止，我的处境如下：

INSERT INTO smallprojects..PhyInv_310QADLockedDet (MasterRecid, location, partnum, qty) 
             SELECT @@IDENTITY, ld_loc, ld_part, ld_qty_oh 
             FROM OPENQUERY(LANSRHQAD, 'SELECT ld_loc,ld_part,ld_qty_oh FROM PUB.ld_det as a left outer join PUB.pt_mstr as b on a.ld_part = b.pt_part where pt_status <> ''OB'' and ld_part not like ''S%'' and ld_part not like ''N%'' and ld_loc = ''310'' ') 

但是，如果PUB.ld_det对该部分有多个条目，这将插入多个部件编号，类似于下面的示例：

以下是数据(PUB.ld_det)：

Part | Date  | Qty
-------------------
1000 | 10-02 | 0
1000 | 10-03 | 2
1001 | 10-2  | 0
1001 | 10-2  | 2

我希望我的结果是插入到表格中，如：

Part | Qty
-------------------
1000 | 2
1001 | 2

目前，它正在以下列方式返回：

Part | Qty
-----------
1000 | 0
1000 | 2
1001 | 0
1001 | 2

所以，当我回去更新这个表时，我只需要希望它找到正确的行。

我怎样才能避免带来倍数，而只带着最高日期进入呢？公开的查询让我很不爽

Answer 1

这里有一个简单的方法，您希望每个部分都有一行：

INSERT INTO smallprojects..PhyInv_310QADLockedDet (MasterRecid, location, partnum, qty) 
     SELECT TOP (1) WITH TIES @@IDENTITY, ld_loc, ld_part, ld_qty_oh 
     FROM OPENQUERY(LANSRHQAD, 'SELECT ld_loc,ld_part,ld_qty_oh FROM PUB.ld_det as a left outer join PUB.pt_mstr as b on a.ld_part = b.pt_part where pt_status <> ''OB'' and ld_part not like ''S%'' and ld_part not like ''N%'' and ld_loc = ''310'' ') 
     ORDER BY ROW_NUMBER() OVER (PARTITION BY ld_part ORDER BY ld_qty_oh DESC);

如果您想要在有领带时重复使用RANK()。