表中有数据列id和pid,其中pid表示父节点id,现要求的,根据ID帅选出所有父节点也就是
select pid from xxx where id='yyy';
select pid from xxx where id='上一条读出来的id' //依次这样读下去..直到pid=null或者0,
.......现在我能想到的就是
用程序循环执行SQL但是这样每次打开关闭连接,损耗有点大,所以到sql板块来求助存储过程..不知道能否实现
select pid from xxx where id='yyy';
select pid from xxx where id='上一条读出来的id' //依次这样读下去..直到pid=null或者0,
.......现在我能想到的就是
用程序循环执行SQL但是这样每次打开关闭连接,损耗有点大,所以到sql板块来求助存储过程..不知道能否实现
如:
with C
as
(select ID,PID from xxx where ID=''
union all
select xxx.ID,xxx.PID from xxx inner join c on c.PID=xxx.ID)
select ID from C
declare @table table (id int,name varchar(1),pid int)
insert into @table
select 1,'a',0 union all
select 2,'b',1 union all
select 3,'c',2 union all
select 4,'d',2 union all
select 5,'e',1 union all
select 6,'f',5 union all
select 7,'g',2 union all
select 8,'h',3 union all
select 9,'i',5
;with maco as(
select * from @table where name='i'
union all
select t.* from @table t ,maco m where t.id=m.pid
)
select * from maco order by id
--找出name=i的上一条的上一条的上一条
/*
id name pid
----------- ---- -----------
1 a 0
5 e 1
9 i 5
*/
(
select * from tb where id='yyy' and PID is not null
union all
select a.* from tb a join ctetb f on a.ID=f.PID
)
select * from ctetb order by ID
--sql200的写法
CREATE FUNCTION f_Pid(@ID char(3))
RETURNS @t_Level TABLE(ID char(3))
AS
BEGIN
INSERT @t_Level SELECT @ID
SELECT @ID=PID FROM tb
WHERE ID=@ID
AND PID IS NOT NULL
WHILE @@ROWCOUNT>0
BEGIN
INSERT @t_Level SELECT @ID
SELECT @ID=PID FROM tb
WHERE ID=@ID
AND PID IS NOT NULL
END
RETURN
END
select tb.*
from tb join dbo.f_Pid('xxx') b
on tb.ID=b.id
CREATE TABLE tb(ID char(3),PID char(3),Name nvarchar(10))
INSERT tb SELECT '001',NULL ,'山东省'
UNION ALL SELECT '002','001','烟台市'
UNION ALL SELECT '004','002','招远市'
UNION ALL SELECT '003','001','青岛市'
UNION ALL SELECT '005',NULL ,'四会市'
UNION ALL SELECT '006','005','清远市'
UNION ALL SELECT '007','006','小分市'
GO--查询指定节点及其所有子节点的函数
CREATE FUNCTION f_Cid(@ID char(3))
RETURNS @t_Level TABLE(ID char(3),Level int)
AS
BEGIN
DECLARE @Level int
SET @Level=1
INSERT @t_Level SELECT @ID,@Level
WHILE @@ROWCOUNT>0
BEGIN
SET @Level=@Level+1
INSERT @t_Level SELECT a.ID,@Level
FROM tb a,@t_Level b
WHERE a.PID=b.ID
AND b.Level=@Level-1
END
RETURN
END
GO
--调用函数查询002及其所有子节点
SELECT a.*
FROM tb a,f_Cid('002') b
WHERE a.ID=b.ID
/*--结果
ID PID Name
------ ------- ----------
002 001 烟台市
004 002 招远市 sql2000的例子
create table tb(pid int,id int)
insert tb
select 10,11 union all
select 11,12 union all
select 12,13 union all
select 13,14 union all
select 100,101 union all
select 101,102 union all
select 102,103 union all
select 1000,1001 union all
select 1001,1002 union all
select 1002,1003 create function GetPTable(@id int) returns @T table(pid int) --找所祖先节点
as
begin
declare @pid as int
select @pid=pid from tb where id=@id
insert @T select @pid
if exists(select 1 from tb where @pid=id)
insert @T select pid from dbo.GetPTable(@pid)
return
endselect * from dbo.GetPTable(14) order by pid
-->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
*/本文来自CSDN博客,转载请标明出处:http://blog.csdn.net/wufeng4552/archive/2009/09/30/4619995.aspx
-->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
*/本文来自CSDN博客,转载请标明出处:http://blog.csdn.net/wufeng4552/archive/2009/09/30/4619995.aspx
(
select * from tb where id='yyy' and PID is not null
union all
select a.* from tb a join ctetb f on a.ID=f.PID
)
select * from ctetb order by ID
CREATE PROCEDURE [dbo].[GetNewsPageaaaaa]
@tagName varchar(20)
AS
if @tagName!='ALL'
BEGIN
DECLARE @size int
;with
ceshi as
(
select * from tag where tag_name=@tagName
union all
select t.* from ceshi j join tag t on j.tag_guid=t.parent_guid
)
SELECT *from ceshi
END
COMMENT '执行完该存储过程后,所有的父节点存储在tmpnode表中,可以select * from tmpnode;得到结果'
BEGIN
declare tmpid INT;
DECLARE tmpchildid INT;
set tmpchildid=childid;
drop table if exists tmpnode;
create table if not EXISTS tmpnode (pid int(6));
lable:LOOP
SELECT pid FROM node where id=tmpchildid into tmpid;
if tmpid=0
then LEAVE lable;
end if;
insert into tmpnode VALUES(tmpid);
set tmpchildid=tmpid;
END LOOP lable;
END
from xxx
connect by pid = id
start with
id = 起始ID