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

问题描述

我有一个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'

这篇关于替换从左外部联接返回的默认空值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！