SQL物料清单示例

目录

物料清单问题

编写一个查询来计算未在BillOfMaterials中列出的产品数量

列出没有子组件的产品ID，包括名称和产品编号

列出不在子组件中的产品ID，包括名称和产品编号

列出作为子组件的产品ID，包括名称和产品编号

AdventureWorks物料清单的最终评论

在这篇文章中，您将学习如何通过解决难题来探索SQL物料清单示例。

在这个谜题中，我们将探讨一个SQL物料清单示例。公司使用BOM（物料清单）来逐项列出用于构建其产品的组件和子组件。

解决难题是学习SQL的好方法。没有什么比练习你所学的更重要的了。一旦你弄清楚了这个谜题，请在评论中发布你的答案，以便我们大家互相学习。

物料清单问题

使用上面的AdventureWorks数据库和图表，提供以下查询：

1. 编写查询以统计BillOfMaterials中未列出的产品数量
2. 检索没有子组件的产品ID，包括名称和产品编号
3. 返回不在子组件中的产品ID，包括名称和产品编号
4. 列出作为子组件的产品ID，包括名称和产品编号

如果您需要帮助了解BOM的工作原理，请查看下图：

中的项目：

• 黄色是不在子组件中的产品。
• 绿色是Bike产品的子组件。
• 蓝色是没有任何子组件的部分。

挂在那里！当您完成这些SQL物料清单示例时，您将成为专业人士！

编写一个查询来计算未在BillOfMaterials中列出的产品数量

要知道BOM中未列出的产品数量，我们需要找出在BOM中未作为产品装配或组件列出的产品数量。

一种方法是在where子句中使用子查询，查找在BillofMaterial表ProductAssemblyID或ComponentID列(粗体文本)中不存在的ProductID引用。

此外，我已用粗体突出显示SQL，不包括已停产或非活动产品。

这样做的查询是：

SELECT Count(1)
FROM   Production.Product P
WHERE  P.SellEndDate is NULL
       AND p.DiscontinuedDate is NULL
       AND NOT EXISTS
       (SELECT 1
        FROM   Production.BillOfMaterials BOM
        WHERE  BOM.ProductAssemblyID = p.ProductID
               OR BOM.ComponentID = p.ProductID
       )

这将返回标量值157。

子查询和主查询通过ProductID关联。ProductID与每个Product的BOM组件和组件进行匹配。

为此，我们为每个产品运行一个查询。我们将产品ProductID与BOM条目相匹配。如果它匹配ComponentID或ProductAssemblyID，它将包含在结果中。

列出没有子组件的产品ID，包括名称和产品编号

为了回答这个问题，我们从上一个答案中获取查询，将其更改为显示产品列值，而不是行数。

如果产品没有在表中作为组件列出，我们可以很容易地推断出该产品没有子BOM组件，因为子组件是在另一个组件中使用的任何产品。您会看到我们使用子查询（粗体文本）来搜索产品组件。

如果没有找到ProductAssemblyID，则NOT EXISTS运算符返回TRUE。

SELECT P.ProductID,
       P.Name,
       P.ProductNumber,
       P.FinishedGoodsFlag,
       P.ListPrice
FROM   Production.Product P
WHERE  P.SellEndDate is NULL
       AND p.DiscontinuedDate is NULL
       AND NOT EXISTS (SELECT 1
                       FROM  Production.BillOfMaterials BOM
                       WHERE P.ProductID = BOM.ProductAssemblyID)

列出不在子组件中的产品ID，包括名称和产品编号

回答这个问题与上一个问题的主要区别在于用于探测BillOfMaterials表的子查询。

对于这个问题，我们关心的是它是否不是子组件。为此，我们更改子查询以BOM搜索ComponentID与ProductID。在这里找到一个匹配意味着我们的产品是一个子组件。当然，我们正在寻找相反的结果，所以我们使用NOT EXISTS限定符来实现它。

SELECT P.ProductID,
       P.Name,
       P.ProductNumber,
       P.FinishedGoodsFlag,
       P.ListPrice
FROM   Production.Product P
WHERE  P.SellEndDate is NULL
       AND p.DiscontinuedDate is NULL
       AND NOT EXISTS (SELECT 1
                       FROM  Production.BillOfMaterials BOM
                       WHERE P.ProductID = BOM.ComponentID)

列出作为子组件的产品ID，包括名称和产品编号

这个问题的答案与上一个问题的答案正好相反。因此，在这种情况下，子查询搜索ComponentID匹配ProductID的BOM表项。

SELECT P.ProductID,
       P.Name,
       P.ProductNumber,
       P.FinishedGoodsFlag,
       P.ListPrice
FROM   Production.Product P
WHERE  P.SellEndDate is NULL
       AND p.DiscontinuedDate is NULL
       AND EXISTS (SELECT 1
                   FROM  Production.BillOfMaterials BOM
                   WHERE P.ProductID = BOM.ComponentID
                  )

AdventureWorks物料清单的最终评论

您可能想知道为什么我在答案中使用子查询而不是joins。我这样做是因为我认为在这类问题中，子查询更容易阅读。由于我们正在测试是否存在，因此该EXISTS子句非常适合这一点，并且可能更容易解释。

此外，通过使用子查询，我还包括BOM表中没有任何条目的产品。如果我的答案使用INNER JOINS了，那么由于它们只包含匹配的行，这些没有材料清单的产品将被排除在外。

当然，我可以使用Outer JOINS和NULL测试来绕过这个问题，但出于某种原因，我尽量避免outer joins。这只是我的偏好，不一定是最佳实践。

本文最初发布于SQL Bill of Materials Example - Essential SQL

https://www.codeproject.com/Articles/5326768/SQL-Bill-of-Materials-Example