SQL物料清单示例

最新推荐文章于 2023-11-08 18:09:03 发布

寒冰屋

最新推荐文章于 2023-11-08 18:09:03 发布

阅读量721

点赞数

分类专栏：数据库文章标签：数据库 sql

原文链接：https://www.codeproject.com/Articles/5326768/SQL-Bill-of-Materials-Example

版权

数据库专栏收录该内容

229 篇文章 14 订阅

订阅专栏

本文通过SQL查询示例详细介绍了如何探索物料清单（BOM），包括计算未在BOM中列出的产品数量，查找没有子组件、不在子组件中以及作为子组件的产品ID，涉及AdventureWorks数据库的Product和BillOfMaterials表。

摘要由CSDN通过智能技术生成

物料清单问题

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

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

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

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

AdventureWorks物料清单的最终评论

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

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

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

物料清单问题

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

编写查询以统计BillOfMaterials中未列出的产品数量
检索没有子组件的产品ID，包括名称和产品编号
返回不在子组件中的产品ID，包括名称和产品编号
列出作为子组件的产品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 <span style="color: #008000;">(SELECT 1
                   FROM  Production.BillOfMaterials BOM
                   WHERE P.ProductID = BOM.ComponentID
                  )</span>