問(wèn)題描述

有人可以解釋為什么在下面的條件下，coalesce 在 where 子句中不起作用嗎?在這種情況下，我們?nèi)绾卧诓桓淖円韵潞喜l件的情況下正確使用合并，并且只針對(duì)被破壞的 = 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'

使用上面的查詢不會(huì)返回任何內(nèi)容.期望的輸出應(yīng)該是葡萄.

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

期望的輸出:

  Grapes

推薦答案

我們可以在這里使用 ROW_NUMBER 來(lái)選擇你想要的優(yōu)先級(jí):

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;

這里的想法是為每個(gè)被損壞的項(xiàng)目分配一個(gè)從 1 到 5 的優(yōu)先級(jí).我們使用 ROW_NUMBER 來(lái)生成一個(gè)序列，它總是從 1 開(kāi)始，這是最高的可用優(yōu)先級(jí).

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.

這篇關(guān)于在 WHERE 子句中正確使用 COALESCE的文章就介紹到這了，希望我們推薦的答案對(duì)大家有所幫助，也希望大家多多支持html5模板網(wǎng)！