如果存在另一个记录，则排除该记录的值 [英] Exclude value of a record in a group if another is present

问题描述

在下面的示例表中，我试图找到一种对所有标记中ID不存在标记"C"的标记求和的金额之和的方法.当ID中确实存在标记"C"时，我希望该ID上的金额总和不包括标记"A"中的金额.作为说明，我想要的输出在底部.我已经考虑过使用分区和EXISTS命令，但是在概念化解决方案时遇到了麻烦.如果你们中的任何一个可以看一下并指出正确的方向，将不胜感激:)

In the example table below, I'm trying to figure out a way to sum amount over id for all marks where mark 'C' doesn't exist within an id. When mark 'C' does exist in an id, I want the sum of amounts over that id, excluding the amount against mark 'A'. As illustration, my desired output is at the bottom. I've considered using partitions and the EXISTS command, but I'm having trouble conceptualizing the solution. If any of you could take a look and point me in the right direction, it would be greatly appreciated :)

样本表:

id   mark  amount
------------------
1    A     1
2    A     3
2    B     2
3    A     2
4    A     1
4    B     3
5    A     1
5    C     3
6    A     2
6    C     2

所需的输出:

id   sum(amount)
-----------------
1    1
2    5
3    2
4    4
5    3
6    2

推荐答案

select 
  id, 
  case 
     when count(case mark when 'C' then 1 else null end) = 0 
     then 
        sum(amount)
     else 
        sum(case when mark <> 'A' then amount else 0 end)
  end
from sampletable
group by id

这篇关于如果存在另一个记录，则排除该记录的值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！