当前位置: 动力学知识库 > 问答 > 编程问答 >

sql - SELECT Query for a Bill of Materials

问题描述:

Our Bill of Materials table has 3 main columns: Part Number, Parent Part, and Level Code (0 being no parent, 3 being a child of a child of a child, etc).

I'd am trying to query based on a single part and get all of its children and their children, so on.

Its seems so simple but I have been struggling with this one. Anyone have a solution they can offer?

As always, I appreciate the help.

Per Randy's request, here is some more info:

PARPRT = Parent

COMPRT = Component

In the screen shot, all the component parts are part of the Bill of Materials for '101002'.

Later down the table, you will see each of those components in COMPRT listed in the PARPRT column with the components that make each of them up.

I want to query the BOM for '101002' and not only get the 4 parts in COMPRT where PARPRT = '101002', but also their COMPRTs and so on.

网友答案:

Thank you for those who suggested CTE's. That was exactly what I was looking for.

Here is the query I ended up with after some trial and error.

USE PartDatabase
GO
DECLARE @TheSinglePart CHAR(30) = '100001';

WITH BOM (PARPRT_02, COMPRT_02, QTYPER_02)
AS
(
-- Anchor member definition
SELECT e.PARPRT_02, e.COMPRT_02, e.QTYPER_02
FROM dbo.Product_Structure AS e
WHERE e.PARPRT_02 = @TheSinglePart
UNION ALL
-- Recursive member definition
SELECT e.PARPRT_02, e.COMPRT_02, e.QTYPER_02
FROM dbo.Product_Structure AS e
INNER JOIN BOM AS d
ON e.PARPRT_02 = d.COMPRT_02
)

SELECT *
FROM BOM;

分享给朋友:
您可能感兴趣的文章:
随机阅读: