如何从SQL表中为表中出现的每个项获取最后一个事务

Question

我需要一些关于如何从sql表中获取每个项的最后一个事务的建议。此外，如果一个项目只记录了第一笔交易或记录了最后一笔交易，则该项目不得出现在列表中。

例如：项目有4个带有时间戳的交易记录。交易分为A、B、C和D。输出必须告诉我每个项目的最后一笔交易，但只记录了交易A的项目或记录了交易D的项目除外。在这种情况下，Item不应出现在输出中。

目前，我可以使用2个额外的视图和一个单独的表来完成这项工作，我需要首先填充该表，然后我可以获得具有当前状态(最后一个事务ID(的项目列表。我还在学习SQL，所以我相信一定有一种更干净的方法可以做到这一点。

样本数据：

交易日期2021年7月01日09:38

项目	TransID
ABC123	A	2021/07/01 01:00
ABC123	B
ABC123	C	2021年7月01日18:16
ABC123	D	2021/07/02 02:55
ABC124	A	2021/07/02 11:33
ABC124	B	2021/07/02 20:12
ABC125	A	2021/07/03 04:50
ABC125	B	2021/07/03 13:28
ABC125	C	2021/07/03 22:07
ABC126	A	2021/07/04 06:45

似乎可以使用ROW_NUMBER和带窗口的COUNT来帮助过滤：

WITH CTE AS(
SELECT Item,
TransID,
DateofTransaction,
ROW_NUMBER() OVER (PARTITION BY Item ORDER BY DateofTransaction DESC) AS RN,
COUNT(CASE WHEN TransID NOT IN ('A','D') THEN 1 END) OVER  (PARTITION BY Item) AS NotAD --REad that it must have at least one transaction that isn't A or D
FROM dbo.YourTable)
SELECT Item,
TransID,
DateofTransaction
FROM CTE
WHERE RN = 1
AND NotAD > 0;

您要做的事情有两个步骤。第一个步骤是只获取那些有多个条目的TransID。第二种方法是按每组取最后一行。

第一次，您可以使用HAVING COUNT > 1筛选表。

对于第二行，我建议按TransID和DateOfTransaction对其余行进行排序。然后你可以

• 使用OVER PARTITION BY为每个事务获取"子组"。确保订购desc
• 然后使用ROWNUMBER()为每个"子组"中的每一行分配一个数字
• 最后使用WHERE ROWNUMBER() = 1获取第一行(因为您订购了desc，所以这是最后一行(