根据案例查询中的计数返回值

Question

我很期待你对这个请求的看法，它看起来超级简单，但我一个人无法解决。

如果表列part_status有一个值，则返回该值；否则返回"many"。

场景1表数据：

Part_Status
'A'
'A'
'A'

返回

Part_Status
'A'

场景2表格数据：

Part_Status
'A'

返回col1值，此处为

Part_Status
'A'

场景3表格数据：

Part_Status
'A'
'E'

返回值

Part_Status
'Many'

第一个想法是用例，如果计数的数量大于1，则返回"许多"，但它从未打印：

select DISTINCT case
when count(PART_STATUS) > 1 THEN
'MANY'
ELSE
PART_STATUS
end
from (select DISTINCT Part_Status
from Inventory_Part
where part_status IN ('A','E')
)
GROUP BY PART_STATUS

返回

Part_Status
'A'
'E'

在第二个想法中，我让"许多"工作，但单个结果是空

select CASE
WHEN COUNT(PART_STATUS) > 1 THEN
'Many'
end AS "PART_STATUS"
FROM (select DISTINCT Part_Status
from Inventory_Part
where part_status IN ('A', 'E'))

返回

Part_Status 
'Many'

那么，如何将两者结合起来呢？

Answer 1

类似的东西？

SQL> with test (part_status) as
2    (select 'A' from dual union all
3     select 'A' from dual
4    )
5  select distinct
6         case when (select count(distinct part_status) from test) > 1 then 'Many'
7              else part_status
8         end
9  from test;
CASE
----
A

---

SQL> with test (part_status) as
2    (select 'A' from dual union all
3     select 'E' from dual
4    )
5  select distinct
6         case when (select count(distinct part_status) from test) > 1 then 'Many'
7              else part_status
8         end
9  from test;
CASE
----
Many
SQL>

Answer 2

我可以解决它。无论如何，我不相信这是明智的方法。

select CASE
when "cnt" > 1 THEN
'Many'
when "cnt" = 1 THEN
PART_STATUS
end
from (select rownum AS "cnt", PART_STATUS
FROM (select DISTINCT Part_Status
from Inventory_Part
where part_status IN ('A', 'E'))
ORDER BY ROWNUM DESC)
fetch first row only