在 WHERE 子句中正确使用 COALESCE

2021-09-24 00:00:00 sql where-clause oracle plsql coalesce

有人可以解释为什么在下面的条件下，coalesce 在 where 子句中不起作用吗?在这种情况下，我们如何在不改变以下合并条件的情况下正确使用合并，并且只针对被破坏的 = Y?

Can someone explain why coalesce doesn't work in the where clause given the following conditions below? How do we use coalesce correctly in this case without changing the below coalesce conditions and only for spoiled = Y?

餐桌水果:

  ITEM_NAME     ITEM_NO     SPOILED
  Apples        A15354        N 
  Bananas       BYHUG1        N
  Grapes        GR0013        Y     
  Oranges       ORULYE        N
  Guavas        GUOIUW        Y

查询:

  select fruit.item_name
  from fruit
  where fruit.item_no = coalesce('A15354','CURR_NOT_IN_TABLE','GR0013','GUOIUW')
  and fruit.spoiled = 'Y'

使用上面的查询不会返回任何内容.期望的输出应该是葡萄.

Using the query above will not return anything. Desired output should be grapes.

期望的输出:

  Grapes

推荐答案

我们可以在这里使用 ROW_NUMBER 来选择你想要的优先级:

We can use ROW_NUMBER here to select what you want with priorities:

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 开始，这是最高的可用优先级.

The idea here is to assign a priority from 1 to 5 for each item which is spoiled. We use ROW_NUMBER to generate a sequence always starting with 1 being the highest available priority.

相关文章