BO一对多关系中统计不含指定商品的购物篮数量

问题场景 #

现有购物篮与商品的一对多关系数据，在BO中呈现为购物篮ID随商品重复的表格：

需要统计不含Pear的购物篮数量，预期结果为2（对应ID1和ID2）。此前尝试的公式均返回错误结果：

• =Count([ID]) Where (Not([Item] ="Pear"))：会统计所有非Pear的行对应的ID，购物篮3因包含Apple/Orange会被重复计数，结果不符合预期
• =Count([ID]) Where (Count([Item]="Pear")<1)：行级的Count无法按购物篮ID分组判断是否存在Pear，逻辑不成立

解决方案（无需拆分查询） #

使用以下公式即可得到正确结果：

=Count(Distinct [ID]) Where (Max(If([Item]="Pear";1;0))=0)

公式原理 #

1. If([Item]="Pear";1;0)：对每行标记，商品为Pear则返回1，否则返回0
2. Max(...)：按购物篮ID（BO上下文自动按ID分组）取最大值，若该购物篮存在Pear，最大值为1，否则为0
3. Where (Max(...)=0)：筛选出所有不含Pear的购物篮ID
4. Count(Distinct [ID])：统计这些符合条件的唯一购物篮数量

也可使用等价的简化写法：

=Count(Distinct [ID]) - Count(Distinct [ID] Where ([Item]="Pear"))

这种方式通过“总购物篮数 - 含Pear的购物篮数”计算，同样能得到预期结果2。

内容的提问来源于stack exchange，提问作者Nixxus