父子节点问题
来源:互联网 发布:qt 编程视频教程 编辑:程序博客网 时间:2024/04/30 10:21
--查询指定节点下的所有子节点:
--SQL Server2005以上版本
-->>TravyLee生成测试数据:
if OBJECT_ID('tempdb..#tb') is not null
drop table #tb
go
create table #tb
(
id int,
fid int
)
go
insert #tb
select 1,0 union all
select 2,1 union all
select 3,1 union all
select 4,2 union all
select 5,3 union all
select 6,5 union all
select 7,6 union all
select 8,4 union all
select 9,6
go
declare @id int
set @id=2
;with t
as
(
select * from #tb where id=@id
union all
select a.* from #tb a inner join t b
on a.fid=b.id
)
select * from t
/*
id fid
-------------
2 1
4 2
8 4
*/
--查询指定字节点下的所有父节点:
declare @fid int
set @fid=4
;with t
as(
select * from #tb where fid=@fid
union all
select a.* from #tb a inner join t b
on a.id=b.fid
)
select * from t order by id
/*
id fid
----------------
1 0
2 1
4 2
8 4
*/
--SQL Server 2000
-->Title:Generating test data
-->Author:wufeng4552
-->Date :2009-09-30 08:52:38
set nocount on
if object_id('tb','U')is not null drop table tb
go
create table tb(ID int, ParentID int)
insert into tb select 1,0
insert into tb select 2,1
insert into tb select 3,1
insert into tb select 4,2
insert into tb select 5,3
insert into tb select 6,5
insert into tb select 7,6
-->Title:查找指定節點下的子結點
if object_id('Uf_GetChildID')is not null drop function Uf_GetChildID
go
create function Uf_GetChildID(@ParentID int)
returns @t table(ID int)
as
begin
insert @t select ID from tb where ParentID=@ParentID
while @@rowcount<>0
begin
insert @t select a.ID from tb a inner join @t b
on a.ParentID=b.id and
not exists(select 1 from @t where id=a.id)
end
return
end
go
select * from dbo.Uf_GetChildID(5)
/*
ID
-----------
6
7
*/
-->Title:查找指定節點的所有父結點
if object_id('Uf_GetParentID')is not null drop function Uf_GetParentID
go
create function Uf_GetParentID(@ID int)
returns @t table(ParentID int)
as
begin
insert @t select ParentID from tb where ID=@ID
while @@rowcount!=0
begin
insert @t select a.ParentID from tb a inner join @t b
on a.id=b.ParentID and
not exists(select 1 from @t where ParentID=a.ParentID)
end
return
end
go
select * from dbo.Uf_GetParentID(2)
/*
ParentID
-----------
1
0
*/
--SQL Server2005以上版本
-->>TravyLee生成测试数据:
if OBJECT_ID('tempdb..#tb') is not null
drop table #tb
go
create table #tb
(
id int,
fid int
)
go
insert #tb
select 1,0 union all
select 2,1 union all
select 3,1 union all
select 4,2 union all
select 5,3 union all
select 6,5 union all
select 7,6 union all
select 8,4 union all
select 9,6
go
declare @id int
set @id=2
;with t
as
(
select * from #tb where id=@id
union all
select a.* from #tb a inner join t b
on a.fid=b.id
)
select * from t
/*
id fid
-------------
2 1
4 2
8 4
*/
--查询指定字节点下的所有父节点:
declare @fid int
set @fid=4
;with t
as(
select * from #tb where fid=@fid
union all
select a.* from #tb a inner join t b
on a.id=b.fid
)
select * from t order by id
/*
id fid
----------------
1 0
2 1
4 2
8 4
*/
--SQL Server 2000
-->Title:Generating test data
-->Author:wufeng4552
-->Date :2009-09-30 08:52:38
set nocount on
if object_id('tb','U')is not null drop table tb
go
create table tb(ID int, ParentID int)
insert into tb select 1,0
insert into tb select 2,1
insert into tb select 3,1
insert into tb select 4,2
insert into tb select 5,3
insert into tb select 6,5
insert into tb select 7,6
-->Title:查找指定節點下的子結點
if object_id('Uf_GetChildID')is not null drop function Uf_GetChildID
go
create function Uf_GetChildID(@ParentID int)
returns @t table(ID int)
as
begin
insert @t select ID from tb where ParentID=@ParentID
while @@rowcount<>0
begin
insert @t select a.ID from tb a inner join @t b
on a.ParentID=b.id and
not exists(select 1 from @t where id=a.id)
end
return
end
go
select * from dbo.Uf_GetChildID(5)
/*
ID
-----------
6
7
*/
-->Title:查找指定節點的所有父結點
if object_id('Uf_GetParentID')is not null drop function Uf_GetParentID
go
create function Uf_GetParentID(@ID int)
returns @t table(ParentID int)
as
begin
insert @t select ParentID from tb where ID=@ID
while @@rowcount!=0
begin
insert @t select a.ParentID from tb a inner join @t b
on a.id=b.ParentID and
not exists(select 1 from @t where ParentID=a.ParentID)
end
return
end
go
select * from dbo.Uf_GetParentID(2)
/*
ParentID
-----------
1
0
*/
- 父子节点问题
- oracle 父子节点 查询
- js获取父子节点
- treeview 父子节点联动
- mysql 父子节点查询
- js-父子节点
- jquery父子同级节点获取
- jquery寻找父子兄弟节点
- TreeList父子节点的选择
- oracle处理父子节点关系
- js获取父子兄弟节点
- 根据父子节点算层级
- TreeList CheckBox 父子节点联动
- 【C#】wpf查找父子节点
- TreeList实现父子节点联动
- 父子窗口焦点问题
- Angular2 父子路由问题
- mysql 递归实例 父子节点层级递归
- 2013 c++ 第二周任务-读取数据到结构体数组
- types of JIT
- JAVA 泛型
- 20130313,微软3月13日发布7个安全补丁
- 设计模式学习6--外观模式
- 父子节点问题
- 关于echo %classpath% 曾经错误的改正
- JSP中常见的错误及其处理方式
- Np16、输入一颗二元树,从上往下按层打印树的每个结点,同一层中按照从左往右的顺序打印。
- Domain 的Value Range 值转换 (Conversion Routine) .
- 主流操作系统中给网卡添加内存IP
- 各种排序算法的实现及其比较
- Unity3d 模型的淡入淡出
- CPU私有变量(per-CPU变量)