根据另一个表中的记录计数更新表

Question

我需要根据另一个表中匹配记录的计数来更新表中的列。

我有3个表:

[EventDescriptions]
EventID, Description, StartDateTime
[EventEntries]
EntryID, EmployeeKey, EventID, Priority
[EventWinners]
WinnerID, EventID, EmployeeKey

当绘制获胜者时，我需要更新EventEntries表中的Priority列，仅针对从今天开始的未来事件以及从今天开始的90天内EventWinners表中找到的员工行。优先级列给那些没有赢得一个事件的人更高的机会赢得下一个事件，优先级1 vs优先级2或3。

• 设置优先级= 1，在EventWinners中找不到EmployeeKey，其中StartDateTime是距今天不超过90天的事件。
• 设置优先级= 2，在EventWinners中只发现1次EmployeeKey，其中事件的StartDateTime从今天开始不超过90天。
• 设置优先级= 3，其中EmployeeKey被发现>= 2在EventWinners中，事件的StartDateTime从今天开始不超过90天

Answer 1

使用CTE或子查询按EmployeeKey获取最近事件的获胜者计数。接下来，将此CTE与EventEntries连接，并仅将EventEntries过滤到未来的事件。现在，您将在上下文中拥有足够的信息，可以根据您的规则设置Priority。

--!!! Please backup your data before running the update, or do it as a transaction and test the result before committing. !!!
WITH [recent-event-winner-counts] AS (
SELECT [EmployeeKey], COUNT(*) AS [Occurrences]
FROM [EventWinners] AS [w]
INNER JOIN [EventDescriptions] AS [d]
ON [w].[EventID] = [d].[EventID]
WHERE [StartDateTime] BETWEEN DATEADD(DAY, -90, GETDATE()) AND GETDATE()
GROUP BY [EmployeeKey]
)
UPDATE [e]; -- <- remove this semicolon when you're ready to run this
SET Priority = CASE
WHEN [Occurrences] IS NULL THEN 1
WHEN [Occurrences] = 1 THEN 2
WHEN [Occurrences] >= 2 THEN 3
ELSE Priority -- leave unchanged
END
FROM [EventEntries] AS [e]
INNER JOIN [EventDescriptions] AS [d]
ON [e].[EventID] = [d].[EventID]
-- left join as we don't care about EmployeeKeys exclusively in EventWinners
LEFT JOIN [recent-event-winner-counts] AS [r]
ON [e].[EmployeeKey] = [r].[EmployeeKey]
WHERE [d].[StartDateTime] > GETDATE(); -- future events only