Originally posted address 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' EL SELECT 6,' C ',' Aaadsfd'Union All Select 7, 'D', 'D22'Union All SELECT 8,' C ',' DDDD512'Union All Slect 9 , 'A', 'aa3223' uion all succ 10,' dd ',' 356 '
INSERT BOM_HEAD SELECT 1, 1, 1, 1, 'Using' Union All SELECT 2, 3, 1, 1, 'Union All SELECT 3, 1, 1, 2,' Union All Select 4, 6, 1, 1, 'Using' Union All SELECT 5, 8, 1, 1, 'Union All SELECT 6, 2, 1, 1,'
INSERT BOM_DETAIL SELECT 1, 1, 2, 1UNION All SELECT 2, 1, 6, 2UNION All SELECT 3, 2, 1, 1UNION All SELECT 4, 3, 4, 1UNION All SELECT 5, 3, 5, 1UNION All SELECT 6, 4, 7, 1Union All Slect 7, 4, 8, 1Union All Slect 8, 5, 9, 1Union All Select 9, 5, 10, 1 union all SELECT 10, 6, 6, 1GO / * - Interval Relationship Description
The PKID in the BOM_HEAD table is a single value. The same master_item's Status is only one line "in use", others are "deactivated" (Note: is the same master_item), through master_item related to the table item's Item Item BOM_DETAIL Table via Master_Item BOM_HEAD_PKID is associated with the BOM_HEAD table, associated with Item table with the item table - * /
/ * - Expand BOM
Item is a BOM composition of the material: to the BOM_HEAD table Find Master_Item = 1 and status = 'Use' This can be found in the BOM_HEAD table in the BOM_DETAIL table, I can get Master_Detail table according to BOM_HEAD_PKID = 1 This material needs to use the material of Childern_Item to 2 and 6, respectively;
In BOM_HEAD is 6 and status = 'use' This can come to BOM_HEAD's PKID to find the record of BOM_HEAD_PKID = 4 in BOM_DETAIL, so you can find that the material of Master_Item is 6. Materials from 7 and 8;
In Bom_HEAD, the master_item value is 8 and status = 'use' This can go to BOM_HEAD's PKID to find the record of BOM_HEAD_PKID = 5 in BOM_DETAIL, so you can find that master_item 8 material needs to use childern_item 9 And 10 materials;
Finally, it is necessary to obtain a tree-shaped structure as shown in the first layer of 1 Brand, Part_no 2nd 2 Brand, Part_no, Qty 6 Brand, Part_no, Qty 6 Brand, Part_no, Qty 8 Brand, Part_no, Qty 8 Brand, Part_no, Qty Fourth Floor 9 Brand, Part_no, Qty 10 Brand, Part_no, Qty - * /
- Expand BOM query function 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 , - Hierarchical SID VARCHAR (8000) - Sort by Sort by this, you can reflect the level of the tree) asbegin declare @L int set @ l = 0 insert @r select @ iTEM, BRAND, Part_no, 0, @ L, Right (1000 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 i.Item, 4) from item i, bom_head h, bom_detail d, @ rr where r.Level=@l-1 and r.Item = h .Master_item and h.status = 'use' and h.pkid = D.BOM_HEAD_PKID AND D.CHILDREN_ITEM = IIITEM END RETURNDGO - Call function Get query results Select Hierarchy = Space (Level * 2) '├─' , Item, Brand, Part_no, Qtyfrom F_bom (1) Order by Sidgo
- Delete DROP TABLE ITEM, BOM_HEAD, BOM_DETAILDROP FUNCTION F_BOM
/*--Test Results
Level Item Brand Part_no Qty ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------ ------ ─ 1 a 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 DDD512 1 ├─ 9 a aa3223 1 ├─ 10 DD 356 1
(The number of rows affects is 12 lines) - * /
