在 SQL Server 中为产品层次结构创建摘要报告

Question

我有一个包含以下列的Products表：

CategoryId, SubCategoryId, ProductId, 
Name (this can be either CategoryName, SubCategoryName, or ProductName)

例如，如果我在子类别Computers中Electronics中有一个名为戴尔笔记本电脑的产品，那么我可能有三行：

CategoryID | SubCategoryId    | ProductID     | Name
-----------+------------------+---------------+-------------
1          | NULL             |    NULL       | Electronics
1          | 18               |    NULL       | Computers
1          | 18               |    123        | Dell Laptop
2          | NULL             |    NULL       | Sporting Goods
2          | 28               |    NULL       | Bicycles

以及包含以下列的Sales表：

CategoryId, SubCategoryId, ProductId, SalesAmount

一些销售在类别级别，其他在子类别级别，一些在产品级别。

所以Sales可能看起来像：

CategoryID | SubCategoryId | ProductID | SalesAmount
-----------+---------------+-----------+----------------
1      | NULL          |  NULL     | 100.00
2      | 28            |  NULL     |  50.00
1      | NULL          |  NULL     | 125.00
1      | 18            |  123      | 425.00

我正在尝试创建一个显示以下内容的报告：

CategoryId, SubCategoryId, ProductId, Name, Total Sales without double or triple counting anything. For the above example, the intended result would be:
CategoryID | SubCategoryId     | ProductID     | Name        | TotalSales
-----------+-------------------+---------------+-------------+-------------
1          |  NULL             |  NULL         | Electronics | $225
1          |  A                |  A123         | Dell Laptop | $425
2          |  D                |  NULL         | Bicycles    |  $50

查询：

SELECT 
p.CategoryId, p.SubCategoryId, p.ProductId, [Name], SUM(SalesAmount)
FROM 
Products p 
LEFT JOIN 
Sales s ON p.CategoryId = s.CategoryId
AND p.SubCategoryId = s.SubCategoryId
AND p.ProductId = s.ProductId
GROUP BY 
p.CategoryId, p.SubCategoryId, p.ProductId, p.[Name];

我的结果：

1   NULL    NULL    Electronics NULL
1    18     NULL    Computers   NULL
1    18     123     Dell Laptop 425.00

Answer 1

您需要 INNER 连接并处理连接条件中的空值：

SELECT 
p.CategoryId, p.SubCategoryId, p.ProductId, [Name], SUM(SalesAmount)
FROM 
Products p 
INNER JOIN 
Sales s ON p.CategoryId = s.CategoryId
AND ISNULL(p.SubCategoryId,0) = ISNULL(s.SubCategoryId,0)
AND ISNULL(p.ProductId,0) = ISNULL(s.ProductId,0)
GROUP BY 
p.CategoryId, p.SubCategoryId, p.ProductId, p.[Name];