如何查询子节点和父节点
来源:互联网 发布:消音器会影响威力知乎 编辑:程序博客网 时间:2024/05/01 11:31
--如何查询子节点和父节点
--创建测试表
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
- 如何查询子节点和父节点
- SQL查询子节点或父节点
- JQuery查询父节点或者子节点
- ORACLE 树形遍历查询根节点、父节点、子节点
- ORACLE 树形遍历查询根节点、父节点、子节点
- TreeView中如何选中一个父节点同时选中所有的子节点和孙节点。。。
- HTML中父节点和子节点
- Oracle树查询(查询所有子节点,父节点等等)
- Oracle树查询(查询所有子节点,父节点等等)
- Oracle树查询(查询所有子节点,父节点等等)
- Oracle树查询(查询所有子节点,父节点等等)
- Oracle树查询(查询所有子节点,父节点等等)
- Oracle树查询(查询所有子节点,父节点等等)
- Oracle树查询(查询所有子节点,父节点等等)
- Mysql中的递归层次查询(根据父节点查找所有的子节点和根据子节点查询所有的父节点)的两种运用
- 有关字符串截取的和子父节点的查询
- 查询子节点
- oracle查询出树形结构的表,父节点数据是子节点的数据和
- 对于 .NET 应用程序中执行 XSLT 转换 INFO: 路线图
- Explore.exe错误解决方法
- vmware的快捷键
- 如何清除VS2005中最近工作的项目
- 在Javascript中为String对象添加trim,ltrim,rtrim
- 如何查询子节点和父节点
- 关于Hibernate的 Batch update returned unexpected row count from update异常
- WINCE应用层设计经验总结-关于用串口发AT命令给蓝牙
- 连接管理器和查询的替换参数
- Spring 配置proxool连接池
- SSIS变量类型映射
- ORACLE 的 6550 错误
- ASP内建对象介绍
- ScriptManager'在命名空间'System.Web.UI'中模稜两可