SQL 解码空值
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/9712416/
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
SQL Decode Null Values
提问by Arav
I have the below query. When type_id is nullI want it to be grouped by the name "unknown".
How can I do it.
I know there is a decode function but I'm not sure how to use it.
我有以下查询。当type_id is null我希望它按名称“未知”分组时。
我该怎么做。
我知道有一个解码功能，但我不知道如何使用它。
select type_id,
name
from test_table
group by decode(type_id,'Unknown'),
name;
how can I do it?
我该怎么做？
回答by Jon Heller
select decode(type_id, null, 'Unknown', type_id), name, count(*)
from
(
select 'asdf' type_id, 'name1' name from dual union all
select 'asdf' type_id, 'name2' name from dual union all
select null type_id, 'name3' name from dual
) test_table
group by type_id,name;
I agree with @sql_mommy that CASEwould probably look better. But I disagree about using TechOnTheNet as your primary source of information. You are usually better off with the official documentation, and the page for DECODEis a good example of why.
我同意 @sql_mommy 的看法，CASE这可能会更好看。但我不同意使用 TechOnTheNet 作为您的主要信息来源。通常最好使用官方文档，DECODE 页面就是一个很好的例子。
DECODEhas some strange behavior: "In a DECODE function, Oracle considers two nulls to be equivalent." That behavior is not mentioned in the TechOnTheNet article.
DECODE有一些奇怪的行为：“在 DECODE 函数中，Oracle 认为两个空值是等价的。” TechOnTheNet 文章中没有提到这种行为。
回答by Chetter Hummin
For null, we have the NVL function. It can be used as follows
对于 null，我们有 NVL 函数。它可以如下使用
select nvl(type_id,'Unknown'),name from test_table group by
type_id,name;
回答by Ollie
You can use either the NVLfunction or COALESCE:
您可以使用NVL函数或COALESCE：
select NVL(type_id, 'Unknown') AS type_id,
name
from test_table
group by NVL(type_id, 'Unknown'),
name;
Or
或者
select COALESCE(type_id, 'Unknown') AS type_id,
name
from test_table
group by COALESCE(type_id, 'Unknown'),
name;
COALESCEis more efficient than NVLas it only evaluates the second argument if the first is NULL whereas NVLevaluates both arguments every time.
COALESCE比NVL因为它只在第一个参数为 NULL 时才计算第二个参数而NVL每次都计算两个参数时更有效。
Hope it helps...
希望能帮助到你...
