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

我有一个2008年的 Microsoft SQL Server 查询，它使用左外部连接从三个表返回数据。很多时候，第二个和第三个表中没有数据，所以我得到一个 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'

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

157815 次浏览
小开
最佳答案

这很简单

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'
小开

In case of MySQL or SQLite the correct keyword is IFNULL (not 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'
小开

MySQL

COALESCE(field, 'default')

例如:

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

此外，还可以使用多个列通过 COALESCE函数检查它们的 NULL。 例如:

mysql> SELECT COALESCE(NULL, 1, NULL);
-> 1
mysql> SELECT COALESCE(0, 1, NULL);
-> 0
mysql> SELECT COALESCE(NULL, NULL, NULL);
-> NULL
小开

对于 神使，你可以使用:

NVL 用于将空值转换为查询输出中的默认值。例如。如果您想用‘ NA’替换空值，那么可以使用这样的代码。

SELECT NVL(columnName,'NA') FROM tableName


提交答案