SQL 使用带有 isnull 和 else 的 CASE 语句
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/41902872/
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
Using CASE statement with isnull and else
提问by Myurathan Kajendran
I have a column [Color] which contains 'Black', 'Red', NULL, 'WW', 'RR'
我有一列 [Color]，其中包含“Black”、“Red”、NULL、“WW”、“RR”
I want to have a column which should have
我想要一个应该有的专栏
if color black then 'B'
if color red then 'r'
if color is Null then 'Empty'
for all other entries 'n/a'
I'm using follwoing, but it shows error
我正在使用以下内容，但它显示错误
SELECT Name,
CASE color
WHEN 'black' THEN 'b'
WHEN 'red' THEN 'r'
WHEN ISNULL(color, 'empty')
else 'n/a'
END AS Color_code
FROM SalesLT.Product;
回答by Hart CO
You can use either CASEstructure to do this, but you can't call a function if you use the CASE fieldname WHENapproach, so you can either use CASE WHEN fieldname condition:
您可以使用任一CASE结构来执行此操作，但如果使用该CASE fieldname WHEN方法则无法调用函数，因此您可以使用CASE WHEN fieldname condition：
SELECT Name,
CASE WHEN color = 'black' THEN 'b'
WHEN color = 'red' THEN 'r'
WHEN color IS NULL THEN 'empty'
else 'n/a'
END AS Color_code
FROM SalesLT.Product;
OR:
或者：
SELECT Name,
CASE color
WHEN 'black' THEN 'b'
WHEN 'red' THEN 'r'
WHEN NULL THEN 'empty'
else 'n/a'
END AS Color_code
FROM SalesLT.Product;
回答by scaisEdge
You could try this way
你可以试试这个方法
SELECT Name,
CASE
WHEN color = 'black' THEN 'b'
WHEN color = 'red' THEN 'r'
WHEN color is null THEN 'empty'
ELSE 'n/a'
END AS Color_code
FROM SalesLT.Product;
