在 WHERE 子句中正确使用 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.
相关文章