postgresql postgres 中空值的选择查询中的默认值
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/35787892/
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
Default value in select query for null values in postgres
提问by Sathesh S
I have a table with sales Id, product code and amount. Some places product code is null. I want to show Missinginstead of null. Below is my table.
我有一张包含销售 ID、产品代码和金额的表格。有些地方产品代码为空。我想显示Missing而不是 null。下面是我的桌子。
salesId prodTypeCode amount
1 123 150
2 123 200
3 234 3000
4 234 400
5 234 500
6 123 200
7 111 40
8 111 500
9 1000
10 123 100
I want to display the total amount for every prodTypeCodewith the option of If the prodTypeCode is null then Missingshould be displayed.
我想显示每个的总金额prodTypeCode，如果 prodTypeCode 为空，则应显示Missing选项。
select (CASE WHEN prodTypeCode IS NULL THEN
'Missing'
ELSE
prodTypeCode
END) as ProductCode, SUM(amount) From sales group by prodTypeCode
Above query giving error. Please suggest me to overcome this issue. I ahve created a SQLFIDDLE
以上查询给出错误。请建议我克服这个问题。我已经创建了一个SQLFIDDLE
回答by Bohemian
The problem is a mismatch of datatypes; 'Missing'is text, but the product type code is numeric.
问题是数据类型不匹配；'Missing'是文本，但产品类型代码是数字。
Cast the product type code to text so the two values are compatible:
将产品类型代码转换为文本，以便这两个值兼容：
select (CASE WHEN prodTypeCode IS NULL THEN
'Missing'
ELSE
prodTypeCode::varchar(40)
END) as ProductCode, SUM(amount) From sales group by prodTypeCode
See SQLFiddle.
请参阅SQLFiddle。
Or, simpler:
或者，更简单：
select coalesce(prodTypeCode::varchar(40), 'Missing') ProductCode, SUM(amount)
from sales
group by prodTypeCode
See SQLFiddle.
请参阅SQLFiddle。
回答by Gordon Linoff
Perhaps you have a type mismatch:
也许您的类型不匹配：
select coalesce(cast(prodTypeCode as varchar(255)), 'Missing') as ProductCode,
SUM(amount)
From sales s
group by prodTypeCode;
I prefer coalesce()to the case, simply because it is shorter.
我喜欢coalesce()的case，只是因为它是短。
