物料清单的递归查询 [英] Recursive Query for Bill Of Materials

问题描述

我最近才开始自学 SQL，并且已经能够从各种教程中拼凑出我需要的几乎所有内容，但是这个教程让我头撞墙.我们有一个表格，其中包含我们产品的所有物料清单信息.我只需要其中的 4 列 - PPN_I、CPN_I、QUANTITY_I、BOMNAME_I - 它们分别是项目编号、原材料编号、数量和 BOMName.许多 BOM 包含子装配体.我需要一个结果集，列出 BOM 的所有组件，而不考虑级别.我很确定我需要使用递归查询，但不能完全得到它，任何帮助将不胜感激.我知道还有其他几个 BOM 问题，但所有这些问题似乎都有不同的表结构.所以 -

I've just started teaching myself SQL recently and have been able to piece together almost everything I need from various tutorials, but this one has me banging my head against the wall. We have a table that contains all the bill of material information for our products. I only need 4 of the columns from it - PPN_I, CPN_I, QUANTITY_I, BOMNAME_I - which are Item Number, Raw Material number, quantity, and the BOMName, respectively. Many of the BOMs contain subassemblies. I need a result set that lists all the components of the BOM, regardless of level. I'm pretty sure I need to use a recursive query but can't quite get it and any help would be appreciated. I know there are several other BOM questions out there, but all of them seem to have different table structures. So -

一开始，我只是想获得一个特定项目的结果，以方便我的猜测和检查工作.完成后，我需要所有项目的报告，或者至少是项目列表.其中许多项目是可配置的，并且具有多个 BOM.为了获得默认设置，我正在寻找一个空白的 BOM 名称.

To start, I was just trying to get the results for one specific item to facilitate my guess and check work. When I'm done I'll need the report for all items, or at least a list of items. Many of these items are configurable and have multiple BOMs. To get the default set up I am looking for a blank BOM name.

我可以运行它并获得第一级:

I can run this and get the first level:

    select bm.ppn_i, bm.cpn_i, bm.bomname_i, bm.QUANTITY_I, 1 as BOMLevel
    from BM010115 bm
    where bm.PPN_I like '0123105-HWT' and bm.BOMNAME_I like ''

问题是一个(或多个)行的 cpn_i 值是一个子组件.要查看子组件的组成部分，我需要将第一个查询的每个结果放回同一个查询中.

The problem is that one (or more) of the rows will have cpn_i value that is a subassembly. To see what makes up the subassemply I need to put each of the results from the first query back into the same query.

    select bm.ppn_i, bm.cpn_i, bm.bomname_i, bm.QUANTITY_I, 2 as BOMLevel
    from BM010115 bm
    where bm.PPN_I like 'ZC-BASESUBLIM' and bm.BOMNAME_I like ''

显然这不是最有效的方法.我试过了，我也试过了，但我似乎无法得到正确的结果.我已经经历了很多不同的版本，这是我最近/最接近的.

Obviously this is not the most effective way of doing this. I've tried and I've tried, but I just can't seem to get the results right. I've gone through lots of different versions of this and here's what I've done most recently/gotten closest.

    With BMStudy as
    (select bm.ppn_i, bm.cpn_i, bm.bomname_i, bm.QUANTITY_I, 1 as BOMLevel
    from BM010115 bm
    where bm.PPN_I like '0123105-HWT' and bm.BOMNAME_I like ''

    UNION ALL

    select bb.ppn_i, ba.cpn_i, bb.bomname_i, ba.quantity_i, 2 as BOMLevel
    from BM010115 bb, BMStudy ba
    where bb.BOMNAME_I like '' and ba.PPN_I = bb.CPN_I)
    select * from BMStudy

这仅返回第一级结果.我不相信任何 BOM 的深度都超过 3 个级别，但我希望有一列指示它是哪个级别.有人能指出我正确的方向或给我一些关于我哪里出错的提示吗?

This only returns the first level of results. I don't believe any of the BOMs are more than 3 levels deep, but I'd like to have a column that indicates which level it is. Can someone point me in the right direction or give me some pointers as to where I went wrong?

TL;DR - 需要拉取 BOM 中所有组件的查询，然后拉取第一个查询的所有结果的 BOM 组件并将其添加到结果集中.

TL;DR - Need query that pulls all components in a BOM, and then pulls the BOM components for all the results of the first query and adds it to the result set.

谢谢

推荐答案

Gordon，我非常感谢你的帮助.你帮我解决了一些基本的缺陷，我完成了一些调整，大部分都拥有我需要的东西......至少现在是这样.这让我发疯了很长一段时间.我确定的代码如下，以防它对其他人有帮助

I cannot thank you enough for all your help, Gordon. You helped me with some of the fundamental flaws and I finished some tweaks and for the most part have what I need.... at least for now. This has been driving me nuts for quite some time. The code I settled on is below in case it could be of help to someone else down the road

  With BMStudy as (
  select bm.ppn_i, bm.cpn_i, bm.bomname_i, bm.QUANTITY_I, 1 as BOMLevel
  from BM010115 bm
  where bm.PPN_I like '0123105-HWT' and bm.BOMNAME_I like ''
  UNION ALL
  select ba.ppn_i, bb.cpn_i, bb.bomname_i, bb.quantity_i, 
  (BOMLevel + 1) as BOMLevel
  from BMStudy ba join
       BM010115 bb
       on ba.cpn_i = bb.ppn_i
  where bb.BOMNAME_I like ''
 )

 select top 1000 BMStudy.*, i.ITEMDESC from BMStudy, iv00101 i
 where CPN_I = itemnmbr
 order by BOMLEVEL, CPN_I ASC
 OPTION (MAXRECURSION 0)

再次感谢！

这篇关于物料清单的递归查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！