请提供 sql 帮助 - 获取客户的最新事件

Question

给定 2 个表

CustomerActivity
CustomerId, ActivityId, CreatedOnDate
1, 1, 8/1/2010
1, 2, 8/15/2010
2, 1, 7/24/2010
2, 2, 8/15/2010

TempUpdateTable
CustomerId, RecentActivityDate
1, NULL
2, NULL

如何使用 CustomerActivity 表填充 TempUpdateTable 中的 NULL？

我的第一次尝试没有成功:

UPDATE [TempUpdateTable]
SET RecentActivityDate = 
(SELECT MAX(CreatedOnDate) FROM CustomerActivity CA WHERE CA.CustomerId = CustomerId )

谢谢，

杆子。

Answer 1

您可以使用 CTE(通用表表达式)查找每个客户的最后一个事件:

;WITH LastActivity AS
(
    SELECT
        CustomerID, ActivityID, CreatedOnDate,
        ROW_NUMBER() OVER(PARTITION BY CustomerId ORDER BY CreatedOnDate DESC) 'RowNum'
    FROM
        dbo.CustomerActivity
)
SELECT * FROM LastActivity
WHERE RowNum = 1

这将为每个客户提供一行，其中包含具有最新日期的事件。 PARTITION BY 按客户对您的数据进行分区，例如每个新客户的计数器再次从 1 开始。 ORDER BY 定义按日期降序排列，因此最新/最新的事件是第一个，行号为 1。

现在您可以使用 CTE 更新您的其他表:

;WITH LastActivity AS
(
    SELECT
        CustomerID, ActivityID, CreatedOnDate,
        ROW_NUMBER() OVER(PARTITION BY CustomerId ORDER BY CreatedOnDate DESC) 'RowNum'
    FROM
        dbo.CustomerActivity
)
UPDATE dbo.TempUpdateTable
SET RecentActivityDate = act.CreatedOnDate
FROM LastActivity act
WHERE dbo.TempUpdateTable.CustomerId = act.CustomerID
AND act.RowNum = 1