sql根据类型字段连接两个实体

在我使用mssql server 2016的microsoft dynamics crm 2016（on prem）中，我尝试创建一个报告（使用reportserver），该报告提供所有打开的Opportunity的最新活动：
我想找到opportunity（filteredopportunity）的最新活动（即filteredactivitypointer）。这个问题的第一个（也是简单的）解决方案（针对mssql的performant查询）是这样提供的：sql query get most recent activiy per account（高效查询）
现在我需要像下面这样扩展场景（遍历从活动到机会的两条可选路径）：

filteredactivitypointer包含一个名为referenceobjecttypecode的字段：该字段包含：1表示与活动相关的活动，在本例中，字段referenceobjectid包含帐户id2表示与联系人相关的活动，在本例中，字段referenceobjectid包含联系人id
如何扩展以下查询。。。

SELECT opp.opportunityid, opp.name as OpportunityName, opp.statecode, opp.statecodename, fac.accountid, fac.name As AccountName, fa.regardingobjecttypecode, fa.activitytypecodename, fa.owneridname, fa.actualend As DateCompleted, fa.description As ActivityDescription
FROM FilteredAccount fac cross apply
     (SELECT TOP 1 *
      FROM FilteredActivityPointer fa
      WHERE fa.regardingobjectid = fac.accountid and fa.statecode = 1 and fa.regardingobjecttypecode=1
      order by fa.actualend desc
     ) fa
JOIN FilteredOpportunity as opp
    ON fac.accountid = opp.accountid
    WHERE opp.statecode = 0

... 因此，我可以通过加入客户和Opp，或者通过加入联系人，加入帐户和帐户来获得最新的filteredactivitypointer。
我不知道如何才能做到这一点我试过这样做，但我迷路了：

SELECT opp.opportunityid, opp.name as OpportunityName, opp.statecode, opp.statecodename, fac.accountid, fac.name As AccountName, fa.regardingobjecttypecode, fa.activitytypecodename, fa.owneridname, fa.actualend As DateCompleted, fa.description As ActivityDescription
FROM FilteredAccount fac cross apply
     (SELECT TOP 1 *
      FROM FilteredActivityPointer fa
      WHERE fa.regardingobjectid = fac.accountid and fa.statecode = 1 and fa.regardingobjecttypecode=1
      order by fa.actualend desc
     ) fa

    FilteredAccount fac2 cross apply
     (SELECT TOP 1 *
      FROM FilteredActivityPointer fa2
      join FilteredContact as co
      ON fa2.regardingobjectid = co.contactid and fa2.regardingobjecttypecode = 2
      join FilteredAccount as ac
      on ac.accountid = opp.account.id;
      WHERE fa.statecode = 1 
      order by fa.actualend desc
     ) fa2

JOIN FilteredOpportunity as opp
    ON fac.accountid = opp.accountid
    WHERE opp.statecode = 0

我尝试在我的microsoftdynamics crm 2016中查找所有打开的Opportunity（filteredopportunity）的最新活动（filteredactivity）。听起来如此简单的事情相当复杂，因为活动可能与接触有关，也可能直接与机会有关。因此，问题归结为如何组合两个结果集，然后从两个结果集的组合中选择最近的活动：一个为每个opportunity提供最近的活动一个为每个帐户（每个联系人）提供最近的活动
这需要工会。为了理解整个问题，我在下面的图表中形象地描述了这种关系

对于结果集1 gordon linoff和delivery，这是一个非常高效的查询：对于结果集2，gmb提供了一个非常聪明的查询：
这两个结果都是有效的和非常有效的。两个结果集可以设计为提供相同的结构：与机会相关的活动。
因此，对于结果集1，我准备了以下查询：

select fac.name as accountname, fa.actualend, fa.description, fa.activitytypecodename, fa.activitytypecode, fac.accountid, opp.opportunityid, opp.name as opportunityname
from FilteredAccount fac cross apply
     (select top (1) fa.*
      from FilteredActivityPointer fa
      where fa.regardingobjectid = fac.accountid and fa.statecode = 1 and fa.activitytypecode != 10004      
      order by fa.actualend desc
     ) fa
Join FilteredOpportunity opp on opp.accountid = fac.accountid and opp.statecode = 0

对于结果集2，我准备了以下查询：

select t.name as accountname, t.actualend, t.description, t.activitytypecodename, t.activitytypecode, t.accountid, t.opportunityid, t.opportunityname
from (
    select ac.accountid, opp.name as opportunityname, opp.opportunityid, ac.name, fa.actualend, fa.description, fa.activitytypecodename, fa.activitytypecode, row_number() over(partition by ac.accountid order by fa.actualend desc) rn
    from FilteredContact co 
    inner join FilteredActivityPointer fa
        on  fa.regardingobjectid = co.contactid 
        and fa.regardingobjecttypecode = 2
        and fa.activitytypecode != 10004        
    inner join FilteredAccount ac 
        on  ac.accountid = co.accountid 
    inner join FilteredOpportunity opp 
        on  opp.accountid = ac.accountid 
        and opp.statecode = 0
) t
where rn = 1

我试图用相等的列名排列结果集。
现在我使用union组合两个结果集：

select allactivities.accountname as accountname, allactivities.actualend, allactivities.description, allactivities.activitytypecodename, allactivities.activitytypecode, allactivities.accountid, allactivities.opportunityid, allactivities.opportunityname
from 
(
    (select fac.name as accountname, fa.actualend, fa.description, fa.activitytypecodename, fa.activitytypecode, fac.accountid, opp.opportunityid, opp.name as opportunityname
    from FilteredAccount fac cross apply
         (select top (1) fa.*
          from FilteredActivityPointer fa
          where fa.regardingobjectid = fac.accountid and fa.statecode = 1 and fa.activitytypecode != 10004      
          order by fa.actualend desc
         ) fa
    Join FilteredOpportunity opp on opp.accountid = fac.accountid and opp.statecode = 0
    )
Union
    (select t.name as accountname, t.actualend, t.description, t.activitytypecodename, t.activitytypecode, t.accountid, t.opportunityid, t.opportunityname
    from 
        (
        select ac.accountid, opp.name as opportunityname, opp.opportunityid, ac.name, fa.actualend, fa.description, fa.activitytypecodename, fa.activitytypecode, row_number() over(partition by ac.accountid order by fa.actualend desc) rn
        from FilteredContact co 
        inner join FilteredActivityPointer fa
            on  fa.regardingobjectid = co.contactid 
            and fa.regardingobjecttypecode = 2
            and fa.activitytypecode != 10004        
        inner join FilteredAccount ac 
            on  ac.accountid = co.accountid 
        inner join FilteredOpportunity opp 
            on  opp.accountid = ac.accountid 
            and opp.statecode = 0
        ) t
    where rn = 1
    )
) allactivities

到目前为止结果还不错。
现在我再次使用了“over（partition）”方法，结果是：

select activity.accountname, activity.actualend, activity.description, activity.activitytypecodename, activity.activitytypecode, activity.accountid, activity.opportunityid, activity.opportunityname
from
    (
        select allactivities.accountname as accountname, allactivities.actualend, allactivities.description, allactivities.activitytypecodename, allactivities.activitytypecode, allactivities.accountid, allactivities.opportunityid, allactivities.opportunityname, row_number() over(partition by allactivities.accountid order by allactivities.actualend desc) row_nr
        from 
        (
            (select fac.name as accountname, fa.actualend, fa.description, fa.activitytypecodename, fa.activitytypecode, fac.accountid, opp.opportunityid, opp.name as opportunityname
            from FilteredAccount fac cross apply
                 (select top (1) fa.*
                  from FilteredActivityPointer fa
                  where fa.regardingobjectid = fac.accountid and fa.statecode = 1 and fa.activitytypecode != 10004      
                  order by fa.actualend desc
                 ) fa
            Join FilteredOpportunity opp on opp.accountid = fac.accountid and opp.statecode = 0
            )
        Union
            (select t.name as accountname, t.actualend, t.description, t.activitytypecodename, t.activitytypecode, t.accountid, t.opportunityid, t.opportunityname
            from 
                (
                select ac.accountid, opp.name as opportunityname, opp.opportunityid, ac.name, fa.actualend, fa.description, fa.activitytypecodename, fa.activitytypecode, row_number() over(partition by ac.accountid order by fa.actualend desc) rn
                from FilteredContact co 
                inner join FilteredActivityPointer fa
                    on  fa.regardingobjectid = co.contactid 
                    and fa.regardingobjecttypecode = 2
                    and fa.activitytypecode != 10004        
                inner join FilteredAccount ac 
                    on  ac.accountid = co.accountid 
                inner join FilteredOpportunity opp 
                    on  opp.accountid = ac.accountid 
                    and opp.statecode = 0
                ) t
            where rn = 1
            )
        ) allactivities
    ) activity
where row_nr = 1

瞧！
请考虑到我使用了添加“联合”的解决方案。上述问题的真正解决方案是由gordonlinoff和gmb提供的。如果没有那些杰出的sql大师的帮助，我将无法做到这一点！

sql根据类型字段连接两个实体

1条答案

相关问题

热门标签

最新问答