Case

SQL CASE 和 ELSE

  • January 2, 2018

您好,我正在考慮以下問題:

CASE 
  WHEN xyz.something = 1 THEN 'SOMETEXT'
  WHEN xyz.somethingelse = 1 THEN 'SOMEOTHERTEXT'
  WHEN xyz.somethingelseagain = 2 THEN 'SOMEOTHERTEXTGOESHERE'
  ELSE 'SOMETHING UNKNOWN'

這是否有效地ELSE介於兩者之間WHEN,即xyz.something = 1優先級高於xyz.somethingelse = 1

如果不是,這是正確的方法嗎?

CASE WHEN xyz.something = 1 THEN 'SOMETEXT' ELSE 
CASE WHEN xyz.somethingelse = 1 THEN 'SOMEOTHERTEXT' ELSE 
CASE WHEN xyz.somethingelseagain = 2 THEN 'SOMEOTHERTEXTGOESHERE'
      ELSE 'SOMETHING UNKNOWN'

如果CASE沒有 WHEN 匹配,則表達式從上到下計算,則使用 ELSE。每個 CASE 都需要一個 END。你的第一個表達:

CASE WHEN xyz.something = 1 THEN 'SOMETEXT'
    WHEN xyz.somethingelse = 1 THEN 'SOMEOTHERTEXT'
    WHEN xyz.somethingelseagain = 2 THEN 'SOMEOTHERTEXTGOESHERE'
    ELSE 'SOMETHING UNKNOWN'
END

類似於:

if xyz.something == 1:
   return 'SOMETEXT'
else if xyz.somethingelse = 1:
   return 'SOMEOTHERTEXT'
...
else:
   return 'SOMETHING UNKNOWN'

你的第二個表達:

CASE WHEN xyz.something = 1 THEN 'SOMETEXT' 
    ELSE CASE WHEN xyz.somethingelse = 1 THEN 'SOMEOTHERTEXT' 
              ELSE CASE WHEN xyz.somethingelseagain = 2 THEN 'SOMEOTHERTEXTGOESHERE'
                        ELSE 'SOMETHING UNKNOWN'
                   END
         END
END

類似於:

if xyz.something == 1:
   return 'SOMETEXT'
else:
   if xyz.somethingelse = 1:
       return 'SOMEOTHERTEXT'
   else:
       if ...

       else:
           return 'SOMETHING UNKNOWN'

兩個表達式都計算為一個字元串。

不確定這是否回答了您的問題,您能否詳細說明您要表達的內容?

引用自:https://dba.stackexchange.com/questions/194354