MySQL 相当于 Oracle 中的 DECODE 函数
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/4706100/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
MySQL equivalent of DECODE function in Oracle
提问by Ali
I am trying to find an equivalent of DECODE function in MySQL. It works like this:
我试图在 MySQL 中找到等效的 DECODE 函数。它是这样工作的：
Select Name, DECODE(Age,
13,'Thirteen',14,'Fourteen',15,'Fifteen',16,'Sixteen',
17,'Seventeen',18,'Eighteen',19,'Nineteen',
'Adult') AS AgeBracket
FROM Person
The DECODE function will compare value of column 'Age' with 13, 14, 15.. and return appropriate string value 'Thirteen', 'Fourteen'.. and if it matches with nothing, then default value of 'Adult' will be returned.
DECODE 函数会将列 'Age' 的值与 13、14、15.. 进行比较，并返回适当的字符串值 'Thirteen'、'Fourteen'.. 如果没有匹配，则返回默认值 'Adult' .
Any ideas which function in MySQL can do this job? Thanks.
MySQL 中的哪些功能可以完成这项工作的任何想法？谢谢。
CLARIFICATION: I agree using CASE is one way of achieving desired result, but I am rather looking for a functionbecause of performance and other reasons.
澄清：我同意使用 CASE 是实现预期结果的一种方式，但由于性能和其他原因，我更愿意寻找一个函数。
回答by Lokesh Kumar
You can use IF()where in Oracle you would have used DECODE().
您可以IF()在 Oracle 中使用您会使用的地方DECODE()。
mysql> select if(emp_id=1,'X','Y') as test, emp_id from emps;
回答by SQLMenace
回答by Allen Kenney
Select Name,
case
when Age = 13 then 'Thirteen'
when Age = 14 then 'Fourteen'
when Age = 15 then 'Fifteen'
when Age = 16 then 'Sixteen'
when Age = 17 then 'Seventeen'
when Age = 18 then 'Eighteen'
when Age = 19 then 'Nineteen'
else 'Adult'
end as AgeBracket
FROM Person
回答by Seth Difley
Another MySQL option that may look more like Oracle's DECODEis a combination of FIELDand ELT. In the code that follows, FIELD()returns the argument list position of the string that matches Age. ELT()returns the string from ELTs argument list at the position provided by FIELD(). For example, if Ageis 14, FIELD(Age, ...)returns 2because 14is the 2nd argument of FIELD(not counting Age). Then, ELT(2, ...)returns 'Fourteen', which is the 2nd argument of ELT(not counting the FIELD()argument). IFNULLreturns the default AgeBracketif no match to Ageis found in the list.
这看起来更像是甲骨文的另一个MySQL选项DECODE是组合FIELD和ELT。在随后的代码中，FIELD()返回匹配 Age 的字符串的参数列表位置。ELT()返回ELTs 参数列表中由 提供的位置的字符串FIELD()。例如，如果Age是14，则FIELD(Age, ...)返回2因为14是FIELD（不计算Age）的第二个参数。然后，ELT(2, ...)返回'Fourteen'，这是ELT（不计算FIELD()参数）的第二个参数。如果在列表中找不到匹配项，则IFNULL返回默认值。AgeBracketAge
Select Name, IFNULL(ELT(FIELD(Age,
13, 14, 15, 16, 17, 18, 19),'Thirteen','Fourteen','Fifteen','Sixteen',
'Seventeen','Eighteen','Nineteen'),
'Adult') AS AgeBracket
FROM Person
While I don't think this is the best solution to the question either in terms of performance or readability it is interesting as an exploration of MySQL's string functions. Keep in mind that FIELD's output does not seem to be case sensitive. I.e., FIELD('A','A')and FIELD('a','A')both return 1.
虽然我不认为这在性能或可读性方面是问题的最佳解决方案，但作为对 MySQL 字符串函数的探索很有趣。请记住，FIELD的输出似乎不区分大小写。也就是说，FIELD('A','A')和FIELD('a','A')都返回1。
回答by user6239614
The example translates directly to:
该示例直接转换为：
Select Name, CASE Age
WHEN 13 then 'Thirteen' WHEN 14 then 'Fourteen' WHEN 15 then 'Fifteen' WHEN 16 then 'Sixteen'
WHEN 17 then 'Seventeen' WHEN 18 then 'Eighteen' WHEN 19 then 'Nineteen'
ELSE 'Adult' END AS AgeBracket
FROM Person
which you may prefer to format e.g. like this:
您可能更喜欢这样格式化：
Select Name,
CASE Age
when 13 then 'Thirteen'
when 14 then 'Fourteen'
when 15 then 'Fifteen'
when 16 then 'Sixteen'
when 17 then 'Seventeen'
when 18 then 'Eighteen'
when 19 then 'Nineteen'
else 'Adult'
END AS AgeBracket
FROM Person
回答by Niteesh Kumar
you can use if() in place of decode() in mySql as follows This query will print all even id row.
您可以使用 if() 代替 mySql 中的 decode() 如下此查询将打印所有偶数 id 行。
mysql> select id, name from employee where id in
-> (select if(id%2=0,id,null) from employee);
回答by Danubian Sailor
If additional table doesn't fit, you can write your own function for translation.
如果附加表不适合，您可以编写自己的函数进行翻译。
The plus of sql function over case is, that you can use it in various places, and keep translation logic in one place.
sql 函数胜过 case 的好处是，你可以在不同的地方使用它，并将翻译逻辑放在一个地方。
回答by vitali_y
Try this:
尝试这个：
Select Name, ELT(Age-12,'Thirteen','Fourteen','Fifteen','Sixteen',
'Seventeen','Eighteen','Nineteen','Adult','Adult','Adult','Adult',
'Adult','Adult','Adult','Adult','Adult','Adult','Adult','Adult','Adult','Adult',
'Adult','Adult','Adult','Adult','Adult','Adult','Adult','Adult','Adult','Adult',
'Adult','Adult','Adult','Adult','Adult','Adult','Adult','Adult','Adult','Adult',
'Adult','Adult','Adult','Adult','Adult','Adult','Adult','Adult','Adult','Adult',
'Adult','Adult','Adult','Adult','Adult','Adult','Adult','Adult','Adult','Adult',
'Adult','Adult','Adult','Adult','Adult','Adult','Adult','Adult','Adult','Adult',
'Adult','Adult','Adult','Adult','Adult','Adult','Adult','Adult','Adult','Adult',
'Adult','Adult','Adult','Adult','Adult','Adult','Adult','Adult','Adult','Adult',
'Adult','Adult','Adult','Adult','Adult','Adult','Adult','Adult','Adult','Adult',
'Adult','Adult','Adult','Adult','Adult','Adult','Adult','Adult','Adult','Adult',
'Adult','Adult','Adult','Adult','Adult','Adult','Adult','Adult','Adult','Adult',
'Adult','Adult','Adult','Adult','Adult','Adult','Adult','Adult','Adult','Adult') AS AgeBracket FROM Person
