嵌套case内的case语句

Question

我会把这个问题说得更准确

我有这些数据

id   product  count  
1      a        10
1      b        20
1      c        10
1      d        30

我想要这样的结果

因为产品A和B都有值，所以我想把它们算作1，所以结果应该是3(不同的A，C，D

如果有任何乘积(A有值，但B没有，反之亦然)，那么结果也必须是3

如果产品A和B都没有值，则结果应为2

如何在oracle plsql中使用case语句来实现这一点

Answer 1

我不确定您是如何定义either count of a or either count of b not both的，但是如果您显式地定义了它，那么您可以尝试这个：

with t as (
    select 1 as id, 'a' as product from dual
    union all
    select 1 as id, 'b' as product from dual
    union all
    select 1 as id, 'c' as product from dual
    union all
    select 1 as id, 'd' as product from dual
) select id, 
        product, 
        count( case when product in ('c', 'd', 'a') then 1 end ) --change 'a' to 'b' to get the the result for 'b'
from t
group by id, product;