我有一个Microsoft SQL Server 2008查询,该查询使用左外部联接从三个表返回数据。很多时候,第二个和第三个表中没有数据,因此我得到一个空值,我认为这是左外部联接的默认值。有没有办法替换select语句中的默认值?我有一个解决方法,可以选择一个表变量,但感觉有点脏。
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'
如果可能的话,我希望数量和常规价格默认为零。
那就像
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'