如何在BOM型层次结构中获取父母的所有叶子

Question

我有一个bom表，

ParentProdID, Qty, ComponentProdID

和其他两个需要连接到BOM表格的条件下的条件：

ParentProdID = ProdTable.ProdID
ProdTable.TraitCode = SuppTable.TraitDesc

我想拥有一张末日：

Parent code | Last component

parent代码填充特质prodid.a and thrait.b或特征的地方树上的最后一个组成部分填充特质。

据我所知，SQL Server应该能够做到这一点，或者是接近的事情，但是我对使用SQL进行递归很新。我看到的大多数解决方案都使用递归CTE以层次结构的方式打印整个BOM，这是两列，但不像我想要的那样干净。

是否可以将特质的东西变成cte，并在找到父母产品的叶子的(可能是一个CTE(中？

父母不必是真正的根节点，可以是任何东西。

例如。对于：

的bom

Guitar(Wood, EndProduct)
-Neck(Wood, Neck)
--Strings(Metal, Neck)
--Hardware(Metal, Neck)
--NeckPiece(Wood, Neck)
-Body(Wood, Body)
--Bottom(Wood, Body)
--Top(Wood, Body)

它打印：

Parent|Leaf
Guitar|NeckPiece
Neck|NeckPiece

如果在SQL Server中无法使用此类功能，将打印所有父母的孩子的列表以及深度计数器，然后安排结果集，以便最高的深度等级在顶部获得功能相同自上而下的查找功能，例如excel中的匹配或vlookup？

Answer 1

第一次使用CTE，因此可能不是最佳解决方案。但是应该足以证明MSSQL中的可能性。

表part：

| id | parent_id | title                    |
| -- | --------- | ------------------------ |
|  1 |      NULL | Guitar(Wood, EndProduct) |
|  2 |         1 | Neck(Wood, Neck)         |
|  3 |         2 | Strings(Metal, Neck)     |
|  4 |         2 | Hardware(Metal, Neck)    |
|  5 |         2 | NeckPiece(Wood, Neck)    |
|  6 |         1 | Body(Wood, Body)         |
|  7 |         6 | Bottom(Wood, Body)       |
|  8 |         6 | Top(Wood, Body)          |

查询：

/*
* the CTE
*/
WITH parts_cte AS
(
    /* anchor:
    *  - part_level: the level in the hierarchy, with root starting at 0 not
    *                really necessary for this query, but still interesting
    *  - root_id:    NULL since this is the root and it has no root above it
    */
    SELECT
        p.id,
        p.parent_id,
        p.title,
        0 AS part_level,
        NULL as root_id
    FROM
        part p
    WHERE
        p.parent_id IS NULL
    /* recursion:
    *  - part_level: increased by one for each recursion step
    *  - root_id:    use the parent's root_id or fall back to parent's id
    */
    UNION ALL
    SELECT
        child.id,
        parent.id,
        child.title,
        parent.part_level + 1,
        ISNULL(parent.root_id, child.parent_id)
    FROM
        part child
        INNER JOIN parts_cte parent ON child.parent_id = parent.id
)
/*
* the actual statement that executes the CTE
*  1. get the parts to use as the parents
*  2. join the CTE as the leafs with matching root_id or matching parent_id
*  3. join the parts again on the leafs to see if any of the leafs have children
*     (which means they are not actually leafs)
*  4. remove lines without a leaf (to exclude the parts selected in 1. that are
*     actually leafs)
*  5. group by the column we want to show (to remove duplicates created by the 3.)
*  6. only keep lines where the leafs don't have any children of their own (see 3.)
*/
SELECT
    parent.id,
    parent.title AS parent,
    leaf.title AS leaf
FROM
    part parent
    LEFT OUTER JOIN parts_cte leaf ON
        parent.id = leaf.root_id
        OR parent.id = leaf.parent_id
    LEFT OUTER JOIN part leaf_child ON leaf.id = leaf_child.parent_id
WHERE
    leaf.id IS NOT NULL
GROUP BY
    parent.id,
    parent.title,
    leaf.title
HAVING
    COUNT(leaf_child.id) = 0
ORDER BY
    parent.id

查询结果：

| id | parent                   | leaf                  |
| -- | ------------------------ | --------------------- |
|  1 | Guitar(Wood, EndProduct) | Bottom(Wood, Body)    |
|  1 | Guitar(Wood, EndProduct) | Hardware(Metal, Neck) |
|  1 | Guitar(Wood, EndProduct) | NeckPiece(Wood, Neck) |
|  1 | Guitar(Wood, EndProduct) | Strings(Metal, Neck)  |
|  1 | Guitar(Wood, EndProduct) | Top(Wood, Body)       |
|  2 | Neck(Wood, Neck)         | Hardware(Metal, Neck) |
|  2 | Neck(Wood, Neck)         | NeckPiece(Wood, Neck) |
|  2 | Neck(Wood, Neck)         | Strings(Metal, Neck)  |
|  6 | Body(Wood, Body)         | Bottom(Wood, Body)    |
|  6 | Body(Wood, Body)         | Top(Wood, Body)       |