SQL 视图 - 如果为空，则添加默认值？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/4956656/
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 View - add default values if null?
提问by laura
I'm generating a view, and I want to populate cells with a pre-defined value if they are null.
我正在生成一个视图，如果单元格为空，我想用预定义的值填充单元格。
The select for the view is:
视图的选择是：
SELECT a_case.Id,
R1.Type AS Referred_by_1,
R1.Type AS Referred_by_2,
R1.Type AS Referred_by_3
FROM dbo.CaseInfo a_case LEFT JOIN
dbo.Referrer R1 ON P.Id = R1.Case_Id AND R1.Seq = 1 LEFT JOIN
dbo.Referrer R2 ON P.Id = R2.Case_Id AND R2.Seq = 2 LEFT JOIN
dbo.Referrer R3 ON P.Id = R3.Case_Id AND R3.Seq = 3
The referrers are optional, and if not specified, I need to populate the field with 'ND'.
推荐人是可选的，如果没有指定，我需要用“ND”填充该字段。
I think I maybe should be using CASE WHEN, but I'm not sure how to integrate that into the existing select...
我想我可能应该使用 CASE WHEN，但我不确定如何将它集成到现有的选择中......
Any advice gratefully received! - L
感谢您收到任何建议！- L
回答by Klaus Byskov Pedersen
回答by onedaywhen
回答by x2.
case when R1.Type is null then 'ND' else R1.Type end AS Referred_by_3
回答by adatapost
You can use IsNull(column_expression,default_value)function.
您可以使用IsNull(column_expression,default_value)功能。
回答by HLGEM
And why not define a default value on the field in the database table instead? Rememeber if you need all records to have this value or one submittedinthe insert or delete and you never want the value to be null, you must fix any exisiting nulls, and then add the default and make sure the field is set to not allow nulls. the advantage of this approach is it ensures that the rule will be followed no matter how the data is entered into the system. If you only want to use a default some of the time, do it inthe application code or stored proc. But if the field should alawys have a value, it is best to do it in the datbase to avoid data integrity issues.
为什么不在数据库表中的字段上定义默认值呢？请记住，如果您需要所有记录都具有此值或在插入或删除中提交一个值，并且您永远不希望该值为空，则必须修复任何现有的空值，然后添加默认值并确保该字段设置为不允许空值。这种方法的优点是可以确保无论数据如何输入系统都遵循规则。如果您只想在某些时候使用默认值，请在应用程序代码或存储过程中进行。但是如果字段应该有一个值，最好在数据库中进行，以避免数据完整性问题。
