如何查询子节点和父节点

 

-- 如何查询子节点和父节点

 

-- 创建测试表

CREATE TABLE DetailAccount(

   id INT PRIMARY KEY,

   parent INT,

   balance FLOAT)

CREATE TABLE RollupAccount(

   id INT PRIMARY KEY,

   parent INT)

INSERT INTO DetailAccount VALUES (3001, 2001, 10)

INSERT INTO DetailAccount VALUES(4001, 3002, 12)

INSERT INTO DetailAccount VALUES(4002, 3002, 14)

INSERT INTO DetailAccount VALUES(3004, 2002, 17)

INSERT INTO DetailAccount VALUES(3005, 2002, 10)

INSERT INTO DetailAccount VALUES(3006, 2002, 25)

INSERT INTO DetailAccount VALUES(3007, 2003, 7)

INSERT INTO DetailAccount VALUES(3008, 2003, 9)

 

INSERT INTO RollupAccount VALUES(3002, 2001)

INSERT INTO RollupAccount VALUES(2001, 1000)

INSERT INTO RollupAccount VALUES(2002, 1000)

INSERT INTO RollupAccount VALUES(2003, 1000)

INSERT INTO RollupAccount VALUES(1000, NULL)

 

-- 查询父节点为的余额总数

WITH Rollup

AS

(

select id ,parent from RollupAccount where id = 1000

union all

select r.id,r.parent from RollupAccount as r join Rollup as rp on r.parent = rp.id

)

--select * from Rollup

select sum(balance) as balance

from DetailAccount as d join Rollup as r

on d.parent = r.id

 

-- 控制树的查询深度

WITH Rollup

AS

(

select id ,parent,0 as depth from RollupAccount where id = 1000

union all

select r.id,r.parent,depth + 1 as depth

from RollupAccount as r join Rollup as rp on r.parent = rp.id

where depth < 1

)

--select * from Rollup

select sum(balance) as balance

from DetailAccount as d join Rollup as r

on d.parent = r.id

 

-- 表函数

create function fn_balanceTree(@start int)

returns table

return

WITH Rollup

AS

(

select id ,parent,0 as depth from RollupAccount where id = @start

union all

select r.id,r.parent,depth + 1 as depth

from RollupAccount as r join Rollup as rp on r.parent = rp.id

)

select * from Rollup

--select * from dbo.fn_balanceTree(1000)

 

-- 查询父节点

WITH Rollup

AS

(

select id ,parent from RollupAccount where id = 3002

union all

select r.id,r.parent from RollupAccount as r join Rollup as rp on r.id = rp.parent

)

select * from Rollup

 

select sum(balance) as balance

from DetailAccount as d join Rollup as r

on d.parent = r.id