SQL Server 中的 DECODE() 函数
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/1559241/
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 SQL Server
提问by Domnic
SELECT PC_COMP_CODE,
'R',
PC_RESUB_REF,
DECODE(PC_SL_LDGR_CODE, '02', 'DR', 'CR'),
PC_DEPT_NO DEPT,
'', --PC_DEPT_NO,
PC_SL_LDGR_CODE + '/' + PC_SL_ACNO,
SUM(DECODE(PC_SL_LDGR_CODE, '02', 1, -1) * PC_AMOUNT),
PC_CHEQUE_NO CHQNO
FROM GLAS_PDC_CHEQUES
WHERE PC_RESUB_REF IS NOT NULL
AND PC_DISCD NOT IN ('d', 'D', 'T')
GROUP BY PC_RESUB_REF,
PC_COMP_CODE,
'JJ',
PC_SL_LDGR_CODE + '/' + PC_SL_ACNO,
PC_DEPT_NO,
PC_CHEQUE_NO,
DECODE(PC_SL_LDGR_CODE, '02', 'DR', 'CR')
Above is a Oracle query; how can I use DECODE() function in SQL Server 2005?
上面是一个Oracle查询；如何在 SQL Server 2005 中使用 DECODE() 函数？
回答by daxsorbito
You could use the 'CASE .. WHEN .. THEN .. ELSE .. END' syntax in SQL.
您可以在 SQL 中使用“CASE .. WHEN .. THEN .. ELSE .. END”语法。
回答by Andrew
If I understand the question correctly, you want the equivalent of decode but in T-SQL
如果我正确理解了这个问题，您需要相当于 decode 但在 T-SQL 中
Select YourFieldAliasName =
CASE PC_SL_LDGR_CODE
WHEN '02' THEN 'DR'
ELSE 'CR'
END
回答by Daniel Alder
Just for completeness (because nobody else posted the most obvious answer):
只是为了完整性（因为没有其他人发布了最明显的答案）：
Oracle:
甲骨文：
DECODE(PC_SL_LDGR_CODE, '02', 'DR', 'CR')
MSSQL (2012+):
MSSQL (2012+)：
IIF(PC_SL_LDGR_CODE='02', 'DR', 'CR')
The bad news:
坏消息：
DECODEwith more than 4 arguments would result in an ugly IIFcascade
DECODE超过 4 个参数会导致丑陋的IIF级联
回答by Anidesh
Create a function in SQL Server as below and replace the DECODEwith dbo.DECODE
创建SQL Server中的功能如下，更换DECODE用dbo.DECODE
CREATE FUNCTION DECODE(@CondField as nvarchar(100),@Criteria as nvarchar(100),
@True Value as nvarchar(100), @FalseValue as nvarchar(100))
returns nvarchar(100)
begin
return case when @CondField = @Criteria then @TrueValue
else @FalseValue end
end
回答by Patipat Kaewlodla
It's easy to do:
很容易做到：
select
CASE WHEN 10 > 1 THEN 'Yes'
ELSE 'No'
END
回答by Jacques Bach
join this "literal table",
加入这个“文字表”，
select
t.c.value('@c', 'varchar(30)') code,
t.c.value('@v', 'varchar(30)') val
from (select convert(xml, '<x c="CODE001" v="Value One" /><x c="CODE002" v="Value Two" />') aXmlCol) z
cross apply aXmlCol.nodes('/x') t(c)
回答by Abdullah
In my Case I used it in a lot of places first example if you have 2 values for select statement like gender (Male or Female) then use the following statement:
在我的案例中，我在很多地方使用了它，第一个示例如果您有 2 个值用于选择语句，如性别（男性或女性），则使用以下语句：
SELECT CASE Gender WHEN 'Male' THEN 1 ELSE 2 END AS Gender
If there is more than one condition like nationalities you can use it as the following statement:
如果有多个条件，如国籍，您可以将其用作以下语句：
SELECT CASE Nationality
WHEN 'AMERICAN' THEN 1
WHEN 'BRITISH' THEN 2
WHEN 'GERMAN' THEN 3
WHEN 'EGYPT' THEN 4
WHEN 'PALESTINE' THEN 5
ELSE 6 END AS Nationality
回答by Rob at TVSeries.com
when I use the function
当我使用该功能时
select dbo.decode(10>1 ,'yes' ,'no')
then say syntax error near '>'
然后在“>”附近说语法错误
Unfortunately, that does not get you around having the CASE clause in the SQL, since you would need it to convert the logical expression to a bit parameter to match the type of the first function argument:
不幸的是，这并不能让您在 SQL 中使用 CASE 子句，因为您需要它将逻辑表达式转换为位参数以匹配第一个函数参数的类型：
create function decode(@var1 as bit, @var2 as nvarchar(100), @var3 as nvarchar(100))
returns nvarchar(100)
begin
return case when @var1 = 1 then @var2 else @var3 end;
end;
select dbo.decode(case when 10 > 1 then 1 else 0 end, 'Yes', 'No');
