SQL查询获取每个帐户的最新活动(高效查询) [英] SQL Query get most recent activiy per account (efficient query)

问题描述

我使用MS SQL Server 2016，并尝试高效地查询MS Dynamics 2016内部部署数据库.您不需要有关Dynamics的特殊知识即可回答我的问题，因为它是SQL而非CRM的问题.

I use MS SQL Server 2016 and try to query the MS Dynamics 2016 on-premise DB efficiently. You don't need special knowledge about Dynamics to answer my problem because it's rather an SQL but a CRM problem.

假设您有实体FilteredAccounts和FilteredActivities(在Dynamics视图中就是名称)

Suppose you have entities FilteredAccounts and FilteredActivities (that's the name in Dynamics View)

FilteredActivites包含以下列:

FilteredActivites have the following columns:

accountid
name

FilteredActivites包含以下列:

FilteredActivites have the following columns:

activityid
actualend [DATETIME]
regardingobjectid (referring to account) 
statecode (int meaning 1 = completed)

我要实现的是所有帐户的列表，该列表向每个帐户显示最近完成的活动.

What I am trying to achieve is a list of all Accounts which shows to each account the most recent completed activity.

我尝试通过以下方式获取最新活动:

I tried to get the most recent activities by:

SELECT TOP 1 * 
FROM [dbo].[FilteredActivityPointer]  As Activities
ORDER BY Activities.actualend DESC

现在我如何有效地加入FilteredAccounts和FilteredActivities，以便在每个FilteredAccount行中，我只能获取最新的FilteredActivity?我计算最新活动性的方法是执行此操作的有效方法吗(即，对于存储在数据库中的活动，> 1.000.000行).是否有更好的方法来执行此操作(例如在添加/更改活动时触发在帐户上存储最新活动ID的脚本.这会损害CRM系统的完整性吗?您不想更改我想您的CRM的系统数据库是这样吗?

Now how do I join FilteredAccounts and FilteredActivities efficiently so that per FilteredAccount row I just get the most recent FilteredActivity? Is the way I compute the most recent actitivity an efficient way of doing this`(i.e. > 1.000.000 rows for activities stored in the DB). Is there a better way of doing this (like triggering the script which stores the id of the most recent activityid on the account whenever an activity is added / changed. Would this harm the integrity of the CRM system? You don't want to change the system db of your crm this way I suppose?

推荐答案

使用正确的索引，交叉应用可能是最快的方法:

With the right indexes, cross apply might be the fastest approach:

select fa.*
from filteredaccounts fac cross apply
     (select top (1) fa.*
      from FilteredActivites fa
      where fa.regardingobjectid = fac.accountid and fa.statecode = 1
      order by fa.actualend desc
     ) fa;

您想要的索引位于 FilteredActivites(关​​于对象ID，帐户ID，实际结束)上.

这篇关于SQL查询获取每个帐户的最新活动(高效查询)的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！