BOM structure query

--> Please attach this information for reference

--> Snails on the black cliff

--> 2009.04.28

Category 1: Query subtree

--> Test environment

If object_id ('dbo. gepro') is not null

Drop table DBO. gepro

Go

Create Table DBO. gepro

(

Id varchar (10), gname varchar (60), PID varchar (10)

)

Go

Insert into gepro ('20140901', Shanghai XX Automotive Electric Appliance Co., Ltd., '0'
Insert into gepro ('381c ', group A of workshop department, '123'
Insert into gepro ('7d1e ', Group A of the production department, '123'
Insert into gepro ('472d ', APL Logistics group, '123'
Insert into gepro ('9b0b', AQ warranty group, '20140901'
Insert into gepro ('4c47', AE development group, '123'
Insert into gepro ('b046 ', group A, '381c'
Insert into gepro ('4c48', group B, '381c'
Insert into gepro ('4e4d ', APL group, '381c'
Insert into gepro ('b25e', AQ group, '4c47'
Insert into gepro ('b4d9', a group of AE, '4e4d'
Insert into gepro ('b046 ', transaction A, '4e4d'
Insert into gepro ('4c48', transaction group B, '4e4d'
Insert into gepro ('4e6d ', APL transaction group, '4c47'
Insert into gepro ('b25e', AQ transaction group, 'b4d9'
Insert into gepro ('b1d9', AE transaction group, 'b25e'

Method 1: CTE

; With ARGs
(
Select * From DBO. gepro where id = '000000'
Union all
Select gepro. * From DBO. gepro, argS where args. ID = DBO. gepro. PID
)
Select * From ARGs;

Id gname PID
------------------------------------------------------------------------------------
0100 Shanghai XX Automotive Electric Appliance Co., Ltd. 0
381c workshop department a group 0100
Group A of 7d1e Production Department 0100
472d APL Logistics group 0100
9b0b AQ Quality Assurance Group 0100
4c47 AE Development Team 0100
B25e AQ group 4c47
4e6d APL transaction group 4c47
B1d9 AE transaction group b25e
B046 group A 381c
4c48 group B 381c
4e4d APL group 381c
B4d9 AE group 4e4d
B046 transaction a 4e4d
4c48 transaction group B 4e4d
B25e AQ transaction group b4d9
B1d9 AE transaction group b25e

(17 rows are affected)
This method is applicable to SQL Server 2005 and later versions;

Method 2: temporary table

Declare @ tab table (ID varchar (10), gname varchar (60), PID varchar (10), lvel INT)
Declare @ level int
Set @ level = 0
Insert into @ Tab
Select ID, gname, PID, @ level from gepro where id = '4e4d'
While @ rowcount> 0
Begin
Set @ level = @ LEVEL + 1
Insert into @ Tab
Select B. ID, B. gname, B. PID, @ level from @ tab A, gepro B where
A. ID = B. PID and A. lvel = @ level-1
End
Select * From @ Tab

Id gname PID lvel
-------------------------------------------------------------------------------------------
4e4d APL group 381c 0
B4d9 AE group 4e4d 1
B046 transaction a 4e4d 1
4c48 transaction group B 4e4d 1
B25e AQ transaction group b4d9 2
B1d9 AE transaction group b25e 3

(6 rows affected)

Category 2: Count subknot points

Method 1: CTE

With sumtb ([ID], [level])
As (
Select [pid], 1
From [gepro]
Where [pid] <> '0'
Union all
Select a. [pid], B. [level] + 1
From [gepro] A, sumtb B
Where a. [ID] = B. [ID]
And a. [pid] <> '0'
)

Select a. [ID], childcounts = count (B. [ID])
From gepro
Left join sumtb B
On a. [ID] = B. [ID]
Group by A. [ID]
Go

(13 rows affected)

Id childcounts
---------------------
0100 16
381c 8
472d 0
4c47 3
4c48 0
4e4d 5
4e6d 0
7d1e 0
9b0b 0
B046 0
B1d9 0
B25e 2
B4d9 2
Warning null values are eliminated for aggregation or other set operations.

(13 rows affected)

Table 'worktable '. Scan count 3, logical reads 252, physical reads 0, pre-reads 0, lob logic reads 0, lob physical reads 0, and LOB pre-reads 0.
Table 'gepro '. Scan count 3, logical reads 33 times, physical reads 0 times, pre-reads 0 times, lob logic reads 0 times, lob physical reads 0 times, and LOB pre-reads 0 times.

(One row is affected)

Method 3: Cross apply

-- Create a function

Create Function suborgs
(
@ Orgid varchar (32)
)
Returns @ tab table (ID varchar (10), gname varchar (60), PID varchar (10 ))
As
Begin
; With ORG (orgid, orname, orpid)
As
(
Select a. ID
, A. gname, A. PID
From gepro
Where a. PID = @ orgid
Union all
Select C. ID, C. gname, C. PID
From gepro C inner join org AA
On AA. orgid = C. PID
)
Insert into @ Tab
Select orgid, orname, orpid from org
Union select @ orgid
Return
End
Go

-- Query the number of subnodes

Select top 1 F. PID, count (*) over () num from gepro cross apply suborgs ('4e4d ') f
Where gepro. ID = f. PID

PID num
---------------------
4e4d 5

Table 'worktable '. Scan count 3, logical reads 15 times, physical reads 0 times, pre-reads 0 times, lob logic reads 0 times, lob physical reads 0 times, and LOB pre-reads 0 times.
Table 'gepro '. 1 scan count, 5 logical reads, 0 physical reads, 0 pre-reads, 0 lob logical reads, 0 physical reads, and 0 lob pre-reads.
Table '# 208cd6fa '. Scan count 1, logical read 1, physical read 0, pre-read 0, lob logical read 0, lob physical read 0, lob pre-read 0.