USE AdventureWorks2014
SELECT
prom.name,
SUM(sod.LineTotal) AS ProductSalesTotal,
SUM(sod.UnitPrice) AS ProductSalesNoDiscount,
SUM(sod.UnitPriceDiscount) AS ProductLosstoDiscount
FROM
Sales.SalesOrderDetail AS sod
FULL JOIN
Production.Product AS prom ON prom.ProductModelID = sod.ProductID
GROUP BY
prom.Name;
这是我到目前为止的代码,不确定为什么第一个后面的3列只说NULL
如果销售订单中有无效产品,我会非常惊讶。至少,它们不会出现在任何合理的数据模型中。
因此,如果您想要所有产品,请使用LEFT JOIN
:
SELECT prom.name,
SUM(sod.LineTotal) AS ProductSalesTotal,
SUM(sod.UnitPrice) AS ProductSalesNoDiscount,
SUM(sod.UnitPriceDiscount) AS ProductLosstoDiscount
FROM Production.Product AS prom LEFT JOIN
Sales.SalesOrderDetail AS sod
ON prom.ProductModelID = sod.ProductID
GROUP BY prom.Name;
如果没有匹配,则SUM()
将是NULL
。这似乎很可疑,但应该看看JOIN
子句。也许ProductModelId
与ProductId
不匹配。
如果对NULL列执行任何操作,将使整个答案为NULL。
因此,如果求和、加、减,甚至试图连接一个字符串,而其中任何值都为NULL,则无论其余数据如何,结果都将为NULL。
这应该可以修复它。它检查列值是否为NULL,然后在尝试求和之前将其设为0。
USE AdventureWorks2014
SELECT prom.name, sum(ISNULL(sod.LineTotal, 0)) as ProductSalesTotal, sum(ISNULL(sod.UnitPrice, 0)) as ProductSalesNoDiscount, sum(ISNULL(sod.UnitPriceDiscount, 0) as ProductLosstoDiscount
FROM Sales.SalesOrderDetail as sod
FULL JOIN Production.Product as prom ON prom.ProductModelID = sod.ProductID
Group by prom.Name;