SQL oracle数据库中的解码函数
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/9354346/
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
Decode function in oracle database
提问by user569125
Can anyone please explain the below SQL statement from Oracle DB:
任何人都可以解释以下来自 Oracle DB 的 SQL 语句：
select decode(TRIM(to_char(SYSDATE,'Day')),'Monday','3','1') from dual
回答by nolt2232
First of all, let's start with the to_char. to_char(SYSDATE,'Day')will give you the day of the week it is today. to_charlets you convert a date (in this case today's date since you've specified sysdate) into a string of a certain format. Take a look here for some other examples of date formats you can use:
首先，让我们从to_char. to_char(SYSDATE,'Day')会给你今天是星期几。to_char允许您将日期（在本例中为您指定后的今天的日期sysdate）转换为特定格式的字符串。在此处查看您可以使用的其他一些日期格式示例：
http://www.techonthenet.com/oracle/functions/to_char.php
http://www.techonthenet.com/oracle/functions/to_char.php
trimremoves leading and trailing whitespace.
trim删除前导和尾随空格。
Now for the decode. You can think of decodelike an if else statement. Take a look at:
现在对于decode. 你可以把它想象成decode一个 if else 语句。看一眼：
http://www.techonthenet.com/oracle/functions/decode.php
http://www.techonthenet.com/oracle/functions/decode.php
In your particular example, you could read this statement as: if today is Monday return 3 else return 1.
在您的特定示例中，您可以将此语句读作：如果今天是星期一，则返回 3，否则返回 1。
decodealso allows you to do things a bit more complex like this:
decode还允许你做一些更复杂的事情：
select decode(TRIM(to_char(SYSDATE,'Day')),'Monday','3','Tuesday',5,'1')
from dual
This would read: if today is Monday return 3, else if today is Tuesday return 5, else return 1
这将是：如果今天是星期一返回 3，否则如果今天是星期二返回 5，否则返回 1
回答by ruakh
That will return 3if it's currently Monday (and the locale is such that the day is rendered as "Monday"), and 1otherwise.
3如果当前是星期一（并且语言环境将这一天呈现为“星期一”），则将返回，否则将返回1。
DECODE(a, b,c, d,e, f,g, ..., h)will compare ato b, d, f, etc., in turn. If ais b, then DECODEreturns c; if ais d, then DECODEreturns e; and so on. If ais not any of these, then DECODEreturns h. (The his optional; the default return-value, if his not given, is NULL.)
DECODE(a, b,c, d,e, f,g, ..., h)将依次a与b、d、f等进行比较。如果a是b，则DECODE返回c；如果a是d，则DECODE返回e；等等。如果a不是其中任何一个，则DECODE返回h。（这h是可选的；如果h没有给出，默认的返回值是NULL。）
回答by Muhammad Yaseen
Below is explanation by parts:
下面分部分说明：
SYSDATE
系统日期
is returning server date like 15-APR-19 and format based on local
正在返回服务器日期，如 15-APR-19 和基于本地的格式
to_char(SYSDATE,'Day')
to_char(SYSDATE,'Day')
is converting into string and return week day like Monday for date 15-APR-19
正在转换为字符串并返回工作日，如星期一，日期为 15-APR-19
TRIM(to_char(SYSDATE,'Day'))
TRIM(to_char(SYSDATE,'Day'))
is removing empty spaces before and after of
正在删除前后的空格
Decode function is similar to if else statements and hence it simplifies code. For Current example if we write in SQL then it should be like below:
解码函数类似于 if else 语句，因此它简化了代码。对于当前示例，如果我们用 SQL 编写，那么它应该如下所示：
CASE
WHEN TRIM(to_char(SYSDATE,'Day')) = 'Monday' THEN '3'
WHEN TRIM(to_char(SYSDATE,'Day')) = 'Tuesday' THEN '4'
ELSE '1'
END
For complete reference see https://docs.oracle.com/cd/B19306_01/server.102/b14200/functions040.htm
如需完整参考，请参阅https://docs.oracle.com/cd/B19306_01/server.102/b14200/functions040.htm
