在日期差异上获得相同 FK 的前一项记录

问题描述：

我需要在一小时内插入来自同一客户的前 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 的前一项记录

相关推荐