在日期差异上获得相同 FK 的前一项记录 [英] Get top one record of same FK on date difference

问题描述

我需要在一小时内插入来自同一客户的前 1 条记录.如果一小时后插入记录，则不需要该记录.请参见下表.这只是 1000 条记录的样本.我使用的是 SQL Server 2005.

I need to get top 1 record from same customer inserted within one hour. If record is inserted after one hour then don't need that one. Please see following table. This is just a sample of 1000s of records. I am using SQL Server 2005.

替代文字 http://img651.imageshack.us/img651/3990/customerssharingmultiple.png

推荐答案

思路如下

• 选择一小时内的所有子订单及其可能的最小(父)ID.(我在这里假设最低的 OrderID 也将是最旧的 OrderID.
• 将这些结果与原始表格结合起来.
• 使用这些结果作为更新语句的基础.

• Select all child orders within one hour with its minimum possible (Parent)ID. (I am assuming here that the lowest OrderID will also be the oldest OrderID).
• Join these results with the original table.
• Use these results as the basis of the update statement.

SQL 语句

UPDATE  Orders
SET     ParentOrderID = p.ParentOrderID
FROM    Orders o
        INNER JOIN (
          SELECT  ParentOrderID = MIN(o1.OrderID), OrderID = o2.OrderID
          FROM    Orders o1
                  LEFT OUTER JOIN Orders o2 ON 
                    o2.CustomerID = o1.CustomerID
                    AND o2.OrderDate > o1.OrderDate
                    AND DATEADD(hh, -1, o2.OrderDate) < o1.OrderDate
          GROUP BY o2.OrderID
        ) p ON p.OrderID = o.OrderID

这篇关于在日期差异上获得相同 FK 的前一项记录的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！