SQL 难题，如何选择零件的最新日期，但每个零件只有 1 行(唯一) [英] SQL conundrum, how to select latest date for part, but only 1 row per part (unique)

问题描述

今天早上我正试图围绕这个问题.

I am trying to wrap my head around this one this morning.

我正在尝试显示零件的 inventory 状态(对于我们的产品)，如果我尝试返回所有零件，此查询只会变得复杂.

I am trying to show inventory status for parts (for our products) and this query only becomes complex if I try to return all parts.

让我来解释一下:

• 单表inventoryReport
• 我有一个我希望显示的 X 个零件的不同列表，其结果必须是 X # 行(每个零件 1 行显示最新的库存条目).
• 表格由带有日期的库存变化条目组成(所以我只需要每个部分的 LATEST 日期条目).
• 该表中包含所有数据，因此无需联接.

目前对于 1 个单部分，它相当简单，我可以通过执行以下 sql(给您一些想法)来完成此操作:

Currently for 1 single part, it is fairly simple and I can accomplish this by doing the following sql (to give you some idea):

SELECT     TOP (1) ldDate, ptProdLine, inPart, inSite, inAbc, ptUm, inQtyOh + inQtyNonet AS in_qty_oh, inQtyAvail, inQtyNonet, ldCustConsignQty, inSuppConsignQty
FROM         inventoryReport
WHERE     (ldPart = 'ABC123')
ORDER BY ldDate DESC

这让我获得了我的 TOP 1 行，每个部分都很简单，但是我需要显示所有 X(比如说 30 个部分).所以我需要 30 行，结果是.当然，简单的解决方案是在我的代码中循环 X# 的 sql 调用(但这会很昂贵)，这就足够了，但为此，我希望更多地使用此 SQL 以减少对 db 的 x# 调用(如果不需要)减少到只有 1 个查询.

that gets me my TOP 1 row, so simple per part, however I need to show all X (lets say 30 parts). So I need 30 rows, with that result. Of course the simple solution would be to loop X# of sql calls in my code (but it would be costly) and that would suffice, but for this purpose I would love to work this SQL some more to reduce the x# calls back to the db (if not needed) down to just 1 query.

从我在这里看到的情况来看，我需要在查找结果集时以某种方式跟踪每个项目的最新日期.

From what I can see here I need to keep track of the latest date per item somehow while looking for my result set.

我最终会做一个

WHERE ldPart in ('ABC123', 'BFD21', 'AA123', etc)

限制我需要的部分.希望我把我的问题说清楚了.如果您有想法，请告诉我.我不能做 DISTINCT 因为行不一样，日期需要是最新的，我最多需要 X 行.

to limit the parts I need. Hopefully I made my question clear enough. Let me know if you have an idea. I cannot do a DISTINCT as the rows are not the same, the date needs to be the latest, and I need a maximum of X rows.

想法?我被卡住了...

Thoughts? I'm stuck...

推荐答案

EDIT:一定要测试每个解决方案的性能.正如这个问题，CTE 方法可能优于使用 ROW_NUMBER.

EDIT: Be sure to test the performance of each solution. As pointed out in this question, the CTE method may outperform using ROW_NUMBER.

;with cteMaxDate as (
    select ldPart, max(ldDate) as MaxDate
        from inventoryReport
        group by ldPart
)
SELECT md.MaxDate, ir.ptProdLine, ir.inPart, ir.inSite, ir.inAbc, ir.ptUm, ir.inQtyOh + ir.inQtyNonet AS in_qty_oh, ir.inQtyAvail, ir.inQtyNonet, ir.ldCustConsignQty, ir.inSuppConsignQty
    FROM cteMaxDate md
        INNER JOIN inventoryReport ir
            on md.ldPart = ir.ldPart
                and md.MaxDate = ir.ldDate

这篇关于SQL 难题，如何选择零件的最新日期，但每个零件只有 1 行(唯一)的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！