sql - Oracle SQL - 按表达式计算组中文本的出现次数，如果 >= 1 个文本，否则为 another_text

Question

我想做一个select语句来概览每个产品的状态。产品的状态可以是OK 或NOT_OK，并且可以对产品进行多次测试。我希望每个产品都处于“最差”状态:如果产品已经至少测试过一次，则我希望获得 NOT_OK NOT_OK 否则 OK 。

---

这是一个小数据示例:

PRODUCT | STATUS    | DATA_PRODUCT_SPECIFIC_TO_KEEP
--------+-----------+--------------------------------
A       | NOT_OK    | AAA
A       | OK        | AAA
B       | OK        | BBB
B       | OK        | BBB
B       | OK        | BBB
C       | NOT_OK    | CCC

这是我期望的结果:

PRODUCT | STATUS    | DATA_PRODUCT_SPECIFIC_TO_KEEP
--------+-----------+--------------------------------
A       | NOT_OK    | AAA
B       | OK        | BBB
C       | NOT_OK    | CCC

---

我尝试使用下面的查询:

SELECT PRODUCT, count(STATUS="NOT_OK"), DATA_PRODUCT_SPECIFIC_TO_KEEP
FROM TABLE 
GROUP BY PRODUCT

但这被错误拒绝了

missing right parenthesis

你有什么想法吗？

Answer 1

要获得该结果，您似乎需要一个 MIN 而不是 COUNT;有了你的数据，这个

select product, min(status), data_product_specific_to_keep
from yourtable
group by product, data_product_specific_to_keep

给予:

A       NOT_OK      AAA                          
B       OK          BBB                          
C       NOT_OK      CCC 

如果你需要处理两个以上的值，你必须定义一个优先级逻辑；您可以使用以下内容来实现它:

select PRODUCT,
       case (status)
           when 2 then 'NOT_OK'
           when 1 then 'AWAIT'
           else 'OK'
       end,
       DATA_PRODUCT_SPECIFIC_TO_KEEP
from (
        select min(
                   case (status)
                     when 'NOT_OK' then 2
                     when 'AWAIT' then 1
                     else 0
                   end
                  ) as status,
               PRODUCT, DATA_PRODUCT_SPECIFIC_TO_KEEP
        from yourTable
        group by PRODUCT, DATA_PRODUCT_SPECIFIC_TO_KEEP
)
order by 1

这样的 table

create table yourTable (PRODUCT, STATUS, DATA_PRODUCT_SPECIFIC_TO_KEEP) as (
    select 'A', 'NOT_OK', 'AAA' from dual union all
    select 'A', 'OK', 'AAA' from dual union all
    select 'B', 'OK', 'BBB' from dual union all
    select 'B', 'AWAIT', 'BBB' from dual union all
    select 'B', 'OK', 'BBB' from dual union all
    select 'C', 'NOT_OK', 'CCC' from dual union all
    select 'C', 'AWAIT', 'CCC' from dual union all
    select 'D', 'NOT_OK', 'DDD' from dual union all
    select 'D', 'NOT_OK', 'DDD' from dual
)

结果是

P CASE(S DAT
- ------ ---
A OK     AAA
B OK     BBB
C AWAIT  CCC
D NOT_OK DDD