-->引用请附上此信息
-->黑木崖上的蜗牛
-->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 次。