From:
ZJCXC (Zou Jian)
Original post:
http://community.9cbs.net/expert/topic/3384/3384941.xml?temp=.6640436
--Test Data
CREATE TABLE [DBO]. [BOM_DETAIL]
[PKID] [INT], - Identity (1, 1) Not Null,
[BOM_HEAD_PKID] [INT] NOT NULL,
[Children_item] [int] Not null,
[Qty] [Decimal] (10, 0) Not null
) On [primary]
Create Table [DBO]. [BOM_HEAD] (
[PKID] [INT], - Identity (1, 1) Not Null,
[Master_Item] [INT] NOT NULL,
[Qty] [INT] NOT NULL,
[Ver] [char] (20) Collate Chinese_prc_ci_as not null,
[Status] [nvarchar] (10) collate chinese_prc_ci_as not null,
) On [primary]
Create Table [DBO]. [Item] (
[item] [int], - Identity (1, 1) Not null,
[BRAND] [NVARCHAR] (10),
[part_no] [nvarchar] (10)
) On [primary]
INSERT ITEM SELECT 1, 'A', 'A1'
Union All SELECT 2, 'B', 'AAAAA'
Union all SELECT 3, 'A', 'Ad'
Union all SELECT 4, 'A', 'SS'
Union all SELECT 5, 'C', '123'
Union all SELECT 6, 'C', 'Aaadsfd'
Union All SELECT 7, 'D', 'D22'
Union all SELECT 8, 'C', 'DDDD512'
Union all SELECT 9, 'A', 'AA3223'
Union All SELECT 10, 'DD', '356'
INSERT BOM_HEAD SELECT 1, 1, 1, 1, '
Union all SELECT 2, 3, 1, 1, '
Union all SELECT 3, 1, 1, 2, 'Disabled'
Union all SELECT 4, 6, 1, 1, '
Union all SELECT 5, 8, 1, 1, '
Union all SELECT 6, 2, 1, 1, '
INSERT BOM_DETAIL SELECT 1, 1, 2, 1
Union all SELECT 2, 1, 6, 2
Union All SELECT 3, 2, 1, 1
Union All SELECT 4, 3, 4, 1
Union All SELECT 5, 3, 5, 1
Union All SELECT 6, 4, 7, 1
Union All SELECT 7, 4, 8, 1
Union All SELECT 8, 5, 9, 1UNION All SELECT 9, 5, 10, 1
Union all SELECT 10, 6, 6, 1
Go
/ * - Interval Relation Description
The PKID in the BOM_HEAD table is a single value. The same master_item's status is only one line to "use", other is "deactivated" (Note: is the same master_item), associated with the table item's ITEM
BOM_DETAIL table is associated with the BOM_HEAD_PKID with the BOM_HEAD table, associated with Item table with item table
- * /
/ * - Expand BOM
The BOM composition of Item is 1 material:
Find Master_Item = 1 and status = 'in the BOM_HEAD table
This can find the record of PKID = 1 in the BOM_HEAD table
I can get Master_Item_pkid = 1 from the BOM_DETAIL table to get Master_Item.
Childern_Item is a material of 2 and 6, respectively;
The master_item value is 6 and status = 'use in bom_head.'
This can go to BOM_HEAD's PKID to 4
Then find the record of BOM_HEAD_PKID = 4 in BOM_DETAIL, so that the material of Master_Item is 6 can be found to use the material of ChildERN_Item from 7 and 8, respectively;
The master_item value in bom_head is 8 and status = 'use'
This can go to BOM_HEAD PKID to 45
Then find the record of BOM_HEAD_PKID = 5 in Bom_Detail, so that the material of Master_Item is 8 is found to use the material of ChildERN_Item as 9 and 10;
In this way
Finally, you should get a tree-shaped structure.
As shown below
First layer 1 BRAND, Part_no
Layer 2 2 Brand, Part_no, Qty 6 Brand, Part_no, Qty
Layer 3 7 Brand, Part_no, Qty 8 Brand, Part_no, Qty
Fourth floor 9 Brand, Part_no, Qty 10 Brand, Part_no, Qty
- * /
- Function of BOM query
Create function f_bom
@item int
Returns @r Table
Item Int,
BRAND NVARCHAR (10),
Part_no nvarchar (10),
Qty Decimal (10, 0), - taken from BOM_DETAIL
Level Int, - level
SID VARCHAR (8000) - Sort by this to sort, you can reflect the hierarchy of the tree
)
AS
Begin
Declare @L INT
Set @ l = 0
INSERT @R select @ iTEM, BRAND, PART_NO, 0, @ L, Right (10000 Item, 4)
From item
Where item = @ iTEM
While @@ rowcount> 0
Begin
Set @ l = @ L 1
INSERT @R select Item, I.BRAND, I.PART_NO, D.QTY, @ L, R.SID ',' Right (10000 IIITEM, 4)
From item i, bom_head h, bom_detail d, @ r r
Where r.Level=@l-1
And r.Item = h.master_itemand h.status = 'use'
And h.pkid = D.BOM_HEAD_PKID
And d.children_Item = i.Item
end
Return
end
Go
- Call function gets the result of the query
SELECT level = space (level * 2) '├─'
, Item, Brand, Part_no, Qty
From f_bom (1)
Order by SID
Go
- Delete test
DROP TABLE ITEM, BOM_HEAD, BOM_DETAIL
Drop Function F_BOM
/*--Test Results
Level Item Brand Part_no Qty
------------------------------------------------ -
├─ 1 a a1 0
├─ 2 B Aaaaa 1
├─6 c aaadsfd 1
├─7 D D22 1
├─ 8 C DDDD512 1
├─ 9 a aa3223 1
├─ 10 DD 356 1
├─6 c aaadsfd 2
├─7 D D22 1
├─ 8 C DDDD512 1
├─ 9 a aa3223 1
├─ 10 DD 356 1
(The number of rows affected is 12 lines)
- * /
