BOM结构查询

-->引用请附上此信息

-->黑木崖上的蜗牛

-->2009.04.28

类别一：查询子树

-->测试环境

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('0100',      , 上海XX汽车电器有限公司,                                            '0   '
insert into Gepro('381C',      , 车间部A组             ,                                            '0100'
insert into Gepro('7D1E',      , 生产部A组             ,                                            '0100'
insert into Gepro('472D',      , APL物流组             ,                                            '0100'
insert into Gepro('9B0B',      , AQ质保组              ,                                            '0100'
insert into Gepro('4C47',      , AE开发组              ,                                            '0100'
insert into Gepro('B046',      , A组                   ,                                            '381C'
insert into Gepro('4C48',      , B组                   ,                                            '381C'
insert into Gepro('4E4D',      , APL一组               ,                                            '381C'
insert into Gepro('B25E',      , AQ一组                ,                                            '4C47'
insert into Gepro('B4D9',      , AE一组                ,                                            '4E4D'
insert into Gepro('B046',      , 事务A                 ,                                            '4E4D'
insert into Gepro('4C48',      , 事务B组               ,                                            '4E4D'
insert into Gepro('4E6D',      , APL事务组             ,                                            '4C47'
insert into Gepro('B25E',      , AQ事务组              ,                                            'B4D9'
insert into Gepro('B1D9',      , AE事务组              ,                                            'B25E'

法一:CTE

;WITH Args AS
(
SELECT * FROM dbo.Gepro WHERE id ='0100'
UNION ALL
SELECT Gepro.* FROM dbo.Gepro ,Args WHERE Args.id = dbo.Gepro.Pid
)
SELECT * FROM Args;

id         GName                                                            Pid
---------- ---------------------------------------------------------------- ----------
0100       上海XX汽车电器有限公司                                            0
381C       车间部A组                                                            0100
7D1E       生产部A组                                                            0100
472D       APL物流组                                                           0100
9B0B       AQ质保组                                                            0100
4C47       AE开发组                                                            0100
B25E       AQ一组                                                             4C47
4E6D       APL事务组                                                           4C47
B1D9       AE事务组                                                            B25E
B046       A组                                                               381C
4C48       B组                                                               381C
4E4D       APL一组                                                            381C
B4D9       AE一组                                                             4E4D
B046       事务A                                                              4E4D
4C48       事务B组                                                             4E4D
B25E       AQ事务组                                                            B4D9
B1D9       AE事务组                                                            B25E

(17 行受影响)
此上方法适用于SQL SERVER 2005及以上版本；

法二：临时表

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一组                                                         381C       0
B4D9       AE一组                                                           4E4D       1
B046       事务A                                                             4E4D       1
4C48       事务B组                                                          4E4D       1
B25E       AQ事务组                                                        B4D9       2
B1D9       AE事务组                                                        B25E       3

(6 行受影响)

类别二：统计子结点数

法一：CTE

WITH sumtb([id],[level]) 
 AS( 
     SELECT [pid],1 
     FROM [Gepro] A 
     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 A
     LEFT JOIN sumtb B 
         ON A.[id]=B.[id] 
 GROUP BY A.[id] 
 GO  

(13 行受影响)

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
警告: 聚合或其他 SET 操作消除了空值。

(13 行受影响)

表 'Worktable'。扫描计数 3，逻辑读取 252 次，物理读取 0 次，预读 0 次，lob 逻辑读取 0 次，lob 物理读取 0 次，lob 预读 0 次。
表 'Gepro'。扫描计数 3，逻辑读取 33 次，物理读取 0 次，预读 0 次，lob 逻辑读取 0 次，lob 物理读取 0 次，lob 预读 0 次。

(1 行受影响)

法三：Cross apply

--创建函数

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 a
   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

--查询子结点数

SELECT TOP 1 f.pid, COUNT(*) OVER() num FROM Gepro CROSS APPLY  subOrgs('4E4D') f
WHERE Gepro.id  = f.pid

pid        num
---------- -----------
4E4D       5

表 'Worktable'。扫描计数 3，逻辑读取 15 次，物理读取 0 次，预读 0 次，lob 逻辑读取 0 次，lob 物理读取 0 次，lob 预读 0 次。
表 'Gepro'。扫描计数 1，逻辑读取 5 次，物理读取 0 次，预读 0 次，lob 逻辑读取 0 次，lob 物理读取 0 次，lob 预读 0 次。
表 '#208CD6FA'。扫描计数 1，逻辑读取 1 次，物理读取 0 次，预读 0 次，lob 逻辑读取 0 次，lob 物理读取 0 次，lob 预读 0 次。