对两个表值求和，如果一列为空，则显示值

Question

>我有两个表的列，我想对两个值求和，但我的一列是空的，当我在下面运行查询时，它显示全部为空。

SELECT
    Product.ProductID, 
    Product.ProductName, 
    (select round(sum(quantity),18, 2)from [ProductPur] where [ProductPur].[ProductID] = product.ProductID) AS Purchased_quantity,
    (select round(sum(quantity),18 ,2)from [PGDN] where [PGDN].[ProductID] = product.ProductID)+(select round(sum(quantity),18 ,2)from [returnnonreturndetails] where [returnnonreturndetails].[ProductID] = product.ProductID) AS Sold_quantity,
     (select round(sum(quantity),18 ,2)from [ProductPur] where [ProductPur].[ProductID] = product.ProductID) - (select round(sum(quantity),18, 2)from [PGDN] where [PGDN].[ProductID] = product.ProductID) AS Stock
FROM Product
ORDER BY Product.ProductName; 

Answer 1

通用 SQL 解决方案使用 COALESCE()：

SELECT p.ProductID, p.ProductName, 
       (select round(sum(quantity),18, 2) from ProductPur pp where pp.[ProductID] = p.ProductID) AS Purchased_quantity,
       (COALESCE( (select round(sum(quantity), 18, 2) from [PGDN] pg where pg.[ProductID] = p.ProductID), 0) +
        COALESCE( (select round(sum(quantity), 18, 2) from returnnonreturndetails rd where rd.[ProductID] = p.ProductID), 0)
       ) AS Sold_quantity,
       (COALESCE( (select round(sum(quantity), 18, 2) from ProductPur pp where pp.[ProductID] = p.ProductID), 0) -
        COALESCE( (select round(sum(quantity), 18, 2) from [PGDN] pg where pg.[ProductID] = p.ProductID), 0)
       ) AS Stock
FROM Product p
ORDER BY p.ProductName; 

在 SQL Server 中，出于性能原因，ISNULL()是更好的选择。

Answer 2

您需要使用isnull函数检查值，即 isnull(quantity,0)

Answer 3

根据您的DBMS，您可以使用IFNULL()(MySQL(，ISNULL()(SQL Server，MS Access(或NVL()(Oracle(。您也可以使用 COALESCE() ，这将是通用解决方案。

所有这些函数的使用方式相同：

ISNULL($statement,$value_if_statement_is_null)

例如

select round(sum(isnull(quantity,0)),18, 2)
from [ProductPur]
where [ProductPur].[ProductID] = product.ProductID)