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

我有一个Microsoft SQL Server 2008查询,它使用左外连接从三个表返回数据 . 很多时候,第二个和第三个表中没有数据,所以我得到一个null,我认为这是左外连接的默认值 . 有没有办法替换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'

如果可能的话,我希望Quantity和RegularPrice默认为零 .

回答(3)

2 years ago

这很简单

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'

2 years ago

如果是 MySQLSQLite ,则正确的关键字是 IFNULL (不是 ISNULL ) .

SELECT iar.Description, 
      IFNULL(iai.Quantity,0) as Quantity, 
      IFNULL(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'

2 years ago

对于 MySQL ,您可以在 SELECT 中使用 COALESCE(field, 'default') ,如:

SELECT
    t.id,
    COALESCE(d.field, 'default')
  FROM
     table t
  LEFT JOIN
     detail d ON t.id = d.item