尝试使用Adventure数据库显示产品名称而不是销售查询的ID

Question

我正在努力使产品名称显示而不是ID。有什么想法吗？

SELECT TOP 10 ProductID, SUM(LineTotal) AS SumOfSales
FROM Sales.SalesOrderDetail AS SOD
JOIN Sales.SalesOrderHeader AS SOH
ON SOD.SalesOrderID = SOH.SalesOrderID
WHERE SOH.OrderDate > '12/31/2012' AND SOH.OrderDate < '1/1/2014'
GROUP BY ProductID
--HAVING SUM(LineTotal) >= 2000000
ORDER BY SUM(LineTotal) DESC

Answer 1

您需要添加产品表，因为产品名称只在那里可用。加入主键和外键上的产品表，从表中获取所需的列。

SELECT TOP 10 sod.ProductID,prd.<add the required column>, SUM(LineTotal) AS SumOfSales
FROM Sales.SalesOrderDetail AS SOD
JOIN Sales.SalesOrderHeader AS SOH
ON SOD.SalesOrderID = SOH.SalesOrderID
join Production.Product prd on prd.ProductID  = sod.ProductID
WHERE SOH.OrderDate > '12/31/2012' AND SOH.OrderDate < '1/1/2014'
GROUP BY sod.ProductID,prd.<add the required column>
--HAVING SUM(LineTotal) >= 2000000
ORDER BY SUM(LineTotal) DESC

Answer 2

您还必须加入到Production.Product表