Mother son coding parts coding child child dosage price amount parts attribute
PID CID QTY COST RICE
0011200001, 4120100011, 7 2 14 purchase part
0011200001, 4160300001, 3 3 9 procurement
0011200001 4 homemade 2040000006 to calculate the cost of child thing ()
BOM2
2040000006 3030100120 1 5 5 purchase part
2040000006 3990120007 71 213 purchase part 3
3990150002 2 homemade 2040000006
BOM3
3990150002 2350000003 1 4 4 purchase part
Implementation status:
Root parent a child parts unit dosage unit price amount
SPID CCID UNITQTY COST PRICE
0011200001, 4120100011, 7 2 14 purchase part
0011200001, 4160300001, 3 3 9 procurement
0011200001, 3030100120, 4, 5 20 purchase part
0011200001 3990120007 284 852 purchase part 3
0011200001 2350000003 8 4 32 procurement
to achieve recursive way
Son a dosage for a dosage of 1 * 4=BOM2 3030100120 BOM1 son the mother of (3030100120) 2040000006 dosage 4
Son a dosage of 284=BOM2 son a dosage of 71 * 3990120007 BOM1 son the mother of (3990120007) 2040000006 dosage 4
Child dosage of dosage of 8=BOM3 2350000003 child parts 1 * BOM2 son the mother of (2350000003) 3990150002 dosage of 2 * BOM3 son the mother of (3990150002) 2040000006 dosage 4
CodePudding user response:
If up to 3 layer, then use LEFT JOIN, or use WITH + recursion