递归比较记录与前一条记录的查询

Question

我有以下表格示例

CREATE TABLE [dbo].[Transactions](
    [TransactionID] [int] IDENTITY(1,1) NOT NULL,
    [TransactionTypeID] [int] NOT NULL,
    [Counter] [int] NOT NULL,
    [GivenCounter] [int] NOT NULL,
    [ExecutionDate] [datetime] NOT NULL,
 CONSTRAINT [PK_Transactions] PRIMARY KEY CLUSTERED ([TransactionID] ASC)

TransactionTypeID字段标识事务的类型:

• 1表示激活
• 2表示消耗量
• 3表示取消

特别是type 3指的是前一个事务的取消。因此，如果在取消之前执行的事务(就ExecutionDate而言)是type 2意味着取消消费，而如果前一个事务是type 1意味着取消是取消激活。

我怎么能只查询含义是停用(type 3与前type 1)的事务?有人能帮忙吗?

编辑:添加一些示例数据以更好地解释

TransactionID   TransactionTypeID   Counter  GivenCounter    ExecutionDate  
--------------  -----------------   -------  ------------    -----------------------
138327              1               1          0             2014-07-20 07:14:08.000
134175              2               0          1             2014-07-21 12:39:12.000
137620              3               1          1             2014-07-22 20:19:06.000
134199              1               1          0             2014-07-23 10:09:52.000
132772              3               0          1             2014-07-24 08:18:23.000

在这个例子中，我的查询应该只提取一条记录(TransactionID 132772)，因为它是取消前一个事务，TransactionTypeID等于1

Answer 1

我会这样做:

SELECT a.transactionid 
FROM   (SELECT *, Row_number() OVER(ORDER BY ExecutionDate) AS Rownumber 
        FROM   transactions) a 
       JOIN (SELECT transactiontypeid, Row_number()  OVER( ORDER BY ExecutionDate) AS Rownumber 
             FROM   transactions) b 
         ON a.rownumber = b.rownumber + 1 
WHERE  a.transactiontypeid = 1 
       AND b.transactiontypeid = 3 

Answer 2

我已经更新了我的答案，你可以试试这个吗?

SELECT *
FROM
(
SELECT 
DENSE_RANK() OVER (PARTITION BY (TransactionTypeID) ORDER BY (ExecutionDate)) AS 'RowNo',
TransactionID, TransactionTypeID, ExecutionDate
FROM dbo.Transactions2
WHERE TransactionTypeID = 3
 ) AS A
WHERE RowNo = 2;