过滤到在链接实体中特定字段中具有零值的实体数量的过滤数

Question

im在CRM动力学中创建一个图表，该图需要显示2列，其中一列显示了与之联系的铅数，其中一个显示了已联系的铅数。稍后的一个工作正常，但是我无法弄清楚如何制作计算空值的过滤器。

<fetch mapping="logical" aggregate="true">
    <entity name="lead">
        <attribute name="entitiy1" groupby="true"alias="_CRMAutoGen_groupby_column_Num_0" />
            <link-entity name="lead" from="leadid" to="leadid" link-type="outer">
               <attribute alias="_CRMAutoGen_aggregate_column_Num_0" name="number_of_interactions" aggregate="countcolumn" distinct="true" />
               <filter type="and">
               <filter type="or">
                   <condition attribute="number_of_interactions" operator="eq" value="0"  />
                   <condition attribute="number_of_interactions" operator="null" />
               </filter>
               </filter>
          </link-entity>
          <link-entity name="lead" from="leadid" to="leadid" link-type="outer">
              <attribute alias="_CRMAutoGen_aggregate_column_Num_13" name="number_of_interactions" aggregate="countcolumn" distinct="true" />    
              <filter type="and">
                  <condition attribute="number_of_interactions" operator="ge" value="1" />
              </filter>
              </link-entity>
   </entity>
</fetch>```

Answer 1

您可以更详细地解释您的代码要做什么，即为什么您要创建两个链接到铅的链接？

？

您当前的代码在我看来是：

select l1.entitiy1 = [some aggregate value related to number_of_interactions]
from lead l1
left join lead l2 on l1.leadid = l2.leadid
left join lead l3 on l1.leadid = l3.leadid
where (l2.number_of_interactions is null or l2.number_of_interactions = 0)
and l3.number_of_interactions >= 1
group by l1.entitiy1