我们可以用 ROW_NUMBER 在这里选择您需要的优先级：

WITH cte AS (
    SELECT f.*, ROW_NUMBER() OVER (ORDER BY DECODE(ITEM_NO, 'A15354', 1,
                                                            'CURR_NOT_IN_TABLE', 2,
                                                            'GR0013', 3,
                                                            'GUOIUW', 4, 5)) rn
    FROM fruit f
    WHERE spoiled = 'Y'
)
SELECT ITEM_NAME
FROM cte
WHERE rn = 1;

这里的想法是为每个损坏的项目分配一个从1到5的优先级。我们使用 ROW_NUMBER 生成总是以1作为最高可用优先级开始的序列。

尽管@tim biegeleisen给出了一个关于如何解决业务问题的完美答案，但它并没有回答op提出的问题。 COALESCE 将返回参数列表中的第一个not null值。如果像这样传递nr个字符串，则值始终是第一个。通常将列作为参数传递，它将返回第一个not null列值
最好的学习方法是反复使用查询和测试

with fruit (ITEM_NAME,ITEM_NO,SPOILED)
AS
(
SELECT 'Apples',   'A15354','N' FROM DUAL UNION 
SELECT 'Bananas',  'BYHUG1','N' FROM DUAL UNION
SELECT 'Grapes',   'GR0013','Y' FROM DUAL UNION     
SELECT 'Oranges',  'ORULYE','N' FROM DUAL UNION
SELECT 'Guavas',   'GUOIUW','Y' FROM DUAL 
)
select fruit.item_name
  from fruit
  where fruit.item_no = COALESCE('A15354','CURR_NOT_IN_TABLE','GR0013','GUOIUW')
Apples

那是因为 COALESCE call only返回'a15354'。另一种测试方法是在select中包含该语句，如下所示：

with fruit (ITEM_NAME,ITEM_NO,SPOILED)
AS
(
SELECT 'Apples',   'A15354','N' FROM DUAL UNION 
SELECT 'Bananas',  'BYHUG1','N' FROM DUAL UNION
SELECT 'Grapes',   'GR0013','Y' FROM DUAL UNION     
SELECT 'Oranges',  'ORULYE','N' FROM DUAL UNION
SELECT 'Guavas',   'GUOIUW','Y' FROM DUAL 
)
select 
   fruit.item_name
 , COALESCE('A15354','CURR_NOT_IN_TABLE','GR0013','GUOIUW')
  from fruit
ITEM_NA COALES
------- ------
Apples  A15354
Bananas A15354
Grapes  A15354
Guavas  A15354
Oranges A15354

如您所见，它为每一行返回相同的值，这仅仅是因为第一个notnull值是字符串a15354。 COALESCE 不是解决你问题的办法，我希望这能解释一下。