替换从左外部联接返回的默认空值

本文介绍了替换从左外部联接返回的默认空值的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！ 问题描述

我有一个Microsoft SQL Server 2008查询，该查询使用左外部联接从三个表中返回数据.很多时候，第二个和第三个表中没有数据，因此我得到一个空值，我认为这是左外部联接的默认值.有没有办法替换select语句中的默认值?我有一个解决方法，可以选择一个表变量，但感觉有点脏.

I have a Microsoft SQL Server 2008 query that returns data from three tables using a left outer join. Many times, there is no data in the second and third tables and so I get a null which I think is the default for left outer join. Is there a way to replace the default values in the select statement? I have a workaround in that I can select into a table variable but it feels a little dirty.

SELECT iar.Description, iai.Quantity, iai.Quantity * rpl.RegularPrice as 'Retail', iar.Compliance FROM InventoryAdjustmentReason iar LEFT OUTER JOIN InventoryAdjustmentItem iai on (iar.Id = iai.InventoryAdjustmentReasonId) LEFT OUTER JOIN Item i on (i.Id = iai.ItemId) LEFT OUTER JOIN ReportPriceLookup rpl on (rpl.SkuNumber = i.SkuNo) WHERE iar.StoreUse = 'yes'

如果可能的话，我希望数量"和常规价格"默认为零.

I would like the Quantity and RegularPrice to default to zero if possible.

推荐答案

就像

IsNull(FieldName, 0)

或更完整地说:

SELECT iar.Description, ISNULL(iai.Quantity,0) as Quantity, ISNULL(iai.Quantity * rpl.RegularPrice,0) as 'Retail', iar.Compliance FROM InventoryAdjustmentReason iar LEFT OUTER JOIN InventoryAdjustmentItem iai on (iar.Id = iai.InventoryAdjustmentReasonId) LEFT OUTER JOIN Item i on (i.Id = iai.ItemId) LEFT OUTER JOIN ReportPriceLookup rpl on (rpl.SkuNumber = i.SkuNo) WHERE iar.StoreUse = 'yes'