Migration

查詢後如何替換列中的值

  • July 6, 2016

我有一列包含數值

我在查詢

SELECT column 
FROM table 
WHERE column='1' OR
column='2' OR 
column='3' OR
column='4'

然後

REPLACE (column, "1", "ABC");
REPLACE (column, "2", "DEF");
REPLACE (column, "3", "GHI");
REPLACE (column, "4", "JKL")

我不確定我是否使用了錯誤的分號。

請幫忙!

您可以CASE將替換用於您正在使用的內容。此外,我已將其修改WHERE為更加精簡;

SELECT
CASE 
   WHEN column = '1' THEN 'ABC'
   WHEN column = '2' THEN 'DEF'
   WHEN column = '3' THEN 'GHI'
   WHEN column = '4' THEN 'JKL'
END AS column
FROM table
WHERE column IN ('1','2','3','4')

請注意,如果column是,int那麼您不需要將值包裝在 ''

SELECT
CASE 
   WHEN column = 1 THEN 'ABC'
   WHEN column = 2 THEN 'DEF'
   WHEN column = 3 THEN 'GHI'
   WHEN column = 4 THEN 'JKL'
END AS column
FROM table
WHERE column IN (1,2,3,4)

這根本不會修改表格,它只會改變數據的顯示方式。

你最好使用:

SELECT CASE
 WHEN column = 1 THEN 'ABC'
 WHEN column = 2 THEN 'DEF'
 WHEN column = 3 THEN 'GHI'
END AS column
FROM table
WHERE ...

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