在 SQL Server 表的子集中查找介于前面和后面的值之间的值

Question

我正在尝试查找特定事务类型的行，该行位于不同事务类型的行之后。我遇到的问题是，有问题的表可以包含实体的每种事务类型的多个实例，我想按顺序配对事务。

确定交易对后，我要将日期值从第一个交易记录类型复制到第二个交易记录类型的匹配行。

下表演示了该问题：

CREATE TABLE [dbo].[Example]
(
    [EntityID] [INT] NOT NULL,
    [TransactionSequenceNumber] [INT] NOT NULL,
    [TransactionType] [VARCHAR](1) NOT NULL,
    [TransactionDate] [DATETIME] NULL
) ON [PRIMARY]
GO
INSERT INTO dbo.Example ( EntityID, TransactionSequenceNumber, TransactionType, TransactionDate ) VALUES  (1, 1, 'A', '2017-01-01 00:00:00')
INSERT INTO dbo.Example ( EntityID, TransactionSequenceNumber, TransactionType, TransactionDate ) VALUES  (1, 2, 'B', '2017-01-02 00:00:00')
INSERT INTO dbo.Example ( EntityID, TransactionSequenceNumber, TransactionType, TransactionDate ) VALUES  (1, 3, 'C', NULL)
INSERT INTO dbo.Example ( EntityID, TransactionSequenceNumber, TransactionType, TransactionDate ) VALUES  (1, 4, 'B', '2017-01-03 00:00:00')
INSERT INTO dbo.Example ( EntityID, TransactionSequenceNumber, TransactionType, TransactionDate ) VALUES  (1, 5, 'C', NULL)
INSERT INTO dbo.Example ( EntityID, TransactionSequenceNumber, TransactionType, TransactionDate ) VALUES  (1, 6, 'D', '2017-01-05 00:00:00')
INSERT INTO dbo.Example ( EntityID, TransactionSequenceNumber, TransactionType, TransactionDate ) VALUES  (2, 1, 'A', '2017-02-01 00:00:00')
INSERT INTO dbo.Example ( EntityID, TransactionSequenceNumber, TransactionType, TransactionDate ) VALUES  (2, 2, 'B', '2017-02-02 00:00:00')
INSERT INTO dbo.Example ( EntityID, TransactionSequenceNumber, TransactionType, TransactionDate ) VALUES  (2, 3, 'C', NULL)
INSERT INTO dbo.Example ( EntityID, TransactionSequenceNumber, TransactionType, TransactionDate ) VALUES  (2, 4, 'B', '2017-02-10 00:00:00')
INSERT INTO dbo.Example ( EntityID, TransactionSequenceNumber, TransactionType, TransactionDate ) VALUES  (2, 5, 'C', NULL)
INSERT INTO dbo.Example ( EntityID, TransactionSequenceNumber, TransactionType, TransactionDate ) VALUES  (2, 6, 'B', '2017-02-13 00:00:00')
INSERT INTO dbo.Example ( EntityID, TransactionSequenceNumber, TransactionType, TransactionDate ) VALUES  (3, 1, 'A', '2017-03-01 00:00:00')
INSERT INTO dbo.Example ( EntityID, TransactionSequenceNumber, TransactionType, TransactionDate ) VALUES  (3, 2, 'B', '2017-03-02 00:00:00')
INSERT INTO dbo.Example ( EntityID, TransactionSequenceNumber, TransactionType, TransactionDate ) VALUES  (3, 3, 'C', NULL)

在此示例中，我想使用前面的类型"B"行中的日期更新类型"C"行。

下面是一个选择查询，它演示了我当前查询的缺陷：

SELECT  
    c.EntityID, c.TransactionSequenceNumber, c.TransactionType,  
    b.EntityID, b.TransactionSequenceNumber, b.TransactionDate 
FROM    
    dbo.Example c
LEFT OUTER JOIN 
    (SELECT 
         EntityID,
         TransactionSequenceNumber,
         TransactionDate
     FROM   
         dbo.Example 
     WHERE  
         TransactionType = 'B') b ON b.EntityID = c.EntityID
                                  AND b.TransactionSequenceNumber < c.TransactionSequenceNumber
WHERE   
    c.TransactionType = 'C'
ORDER BY 
    c.EntityID, c.TransactionSequenceNumber

从结果中可以看出，每个"C"都与每个序列号较小的"B"匹配。如何将查询更改为仅将每个"C"与前面的"B"配对？

谢谢。

Answer 1

您可以使用

OUTER APPLY来获取正确的日期：

SELECT *
FROM dbo.Example A
OUTER APPLY (SELECT TOP 1 *
             FROM dbo.Example
             WHERE EntityID = A.EntityId
             AND TransactionSequenceNumber < A.TransactionSequenceNumber 
             AND TransactionType = 'B'
             ORDER BY TransactionSequenceNumber DESC) B
WHERE A.TransactionType = 'C'
;

如果要更新表的日期，请使用：

UPDATE A
SET A.TransactionDate = B.TransactionDate 
FROM dbo.Example A
OUTER APPLY (SELECT TOP 1 *
             FROM dbo.Example
             WHERE EntityID = A.EntityId
             AND TransactionSequenceNumber < A.TransactionSequenceNumber 
             AND TransactionType = 'B'
             ORDER BY TransactionSequenceNumber DESC) B
WHERE A.TransactionType = 'C'
;