从Server中的上一行获取值的自联接表

Question

我在Server 2008中有一个类似于下面的表

id, userId, eventDate, typeId, data
1, 5, 01-01-2017, 5, 1234
2, 5, 01-01-2017, 9, 1255
3, 5, 02-01-2017, 4, 5325
4, 5, 03-01-2017, 30, null
5, 8, 10-01-2017, 9, 2551
6, 8, 15-01-2017, 2, 3252
7, 8, 20-01-2017, 3, 2155,
8, 8, 21-01-2017, 100, null

这里有几行，其中有两个特定的typeId值，它们缺少最后一个data列。那些typeIds是30和100。我试图用typeid =9查找前一行，并将最后一列复制到空列。

我以为这样的东西会管用的。

SELECT * FROM UserEvents ue1 WITH(NOLOCK)
  INNER JOIN UserEvents ue2 ON ue1.userid = ue2.userid and ue1.eventDate > ue2.eventDate and ue2.typeId = 9
  LEFT OUTER JOIN UserEvents ue3 ON (ue1.userid = ue3.userid AND ue2.eventDate > ue3.eventDate AND ue3.typeId = 9)
WHERE ue1.typeId = 100 and ue3.id IS NULL

但是，这总是typeId =9的第一行，而不是最后一行，就在带有typeId = 100的行之前。

我想要的结果是(没有* *)：

id, userId, eventDate, typeId, data
1, 5, 01-01-2017, 5, 1234
2, 5, 01-01-2017, 9, 1255
3, 5, 02-01-2017, 4, 5325
4, 5, 03-01-2017, 30, *1255*
5, 8, 10-01-2017, 9, 2551
6, 8, 15-01-2017, 2, 3252
7, 8, 20-01-2017, 3, 2155,
8, 8, 21-01-2017, 100, *2551*

Answer 1

您可以使用OUTER APPLY

SELECT A.id,
       A.userid,
       A.eventDate,
       A.typeId,
       ISNULL(A.data,B.data) data
FROM dbo.UserEvents A
OUTER APPLY (SELECT TOP 1 data
             FROM dbo.UserEvents
             WHERE typeId = 9
             AND id < A.id
             ORDER BY id DESC) B;

它的Here is a demo。

其结果是：

╔════╦════════╦════════════╦════════╦══════╗
║ id ║ userid ║ eventDate  ║ typeId ║ data ║
╠════╬════════╬════════════╬════════╬══════╣
║  1 ║      5 ║ 01-01-2017 ║      5 ║ 1234 ║
║  2 ║      5 ║ 01-01-2017 ║      9 ║ 1255 ║
║  3 ║      5 ║ 02-01-2017 ║      4 ║ 5325 ║
║  4 ║      5 ║ 03-01-2017 ║     30 ║ 1255 ║
║  5 ║      8 ║ 10-01-2017 ║      9 ║ 2551 ║
║  6 ║      8 ║ 15-01-2017 ║      2 ║ 3252 ║
║  7 ║      8 ║ 20-01-2017 ║      3 ║ 2155 ║
║  8 ║      8 ║ 21-01-2017 ║    100 ║ 2551 ║
╚════╩════════╩════════════╩════════╩══════╝