树形数据：从子节点得到所有父节点和该子节点本身

Question

CREATE TABLE  CS( ID  INT  ,PARENT_ID  INT)INSERT INTO CS VALUES (2,NULL)
INSERT INTO CS VALUES (1,NULL)
INSERT INTO CS VALUES (100,1)
INSERT INTO  CS VALUES (200,2)
INSERT INTO  CS VALUES (2000,200)
INSERT INTO  CS VALUES (1000,100)
INSERT INTO  CS VALUES (20000,2000)
INSERT INTO  CS VALUES (2111,200)
需要得到的结果：
如节点 200
得到
200  2
2   null
如节点  20000
得到
20000 2000
2000  200
2111  200
200   2
2     null
 

Answer 1

BOM取父节点
--生成测试数据
create table BOM(ID VARCHAR(10),PID VARCHAR(10))
insert into BOM select 'a',NULL
insert into BOM select 'b','a'
insert into BOM select 'c','a'
insert into BOM select 'd','b'
insert into BOM select 'e','b'
insert into BOM select 'f','c'
insert into BOM select 'g','c'
go--创建用户定义函数
create function f_getParent(@ID VARCHAR(10))
returns varchar(8000)
as
begin
    declare @i int,@ret varchar(8000)
    declare @t table(ID VARCHAR(10),PID VARCHAR(10),Level INT)
    set @i = 1
    insert into @t select ID,PID,@i from BOM where ID = @ID
    
    while @@rowcount<>0
    begin
        set @i = @i + 1
        
        insert into @t 
        select 
            a.ID,a.PID,@i 
        from 
            BOM a,@t b 
        where 
            a.ID=b.PID and b.Level = @i-1
    end
    
    select @ret = isnull(@ret,'')+'-'+ID from @t
    set @ret=reverse(stuff(@ret,1,1,''))
    return @ret
end
go--执行查询
select ID,isnull(dbo.f_getParent(ID),'') as path from BOM group by ID
go--输出结果
/*
ID         path
---------- ------------
a          a
b          a-b
c          a-c
d          a-b-d
e          a-b-e
f          a-c-f
g          a-c-g  
*/--删除测试数据
drop function f_getParent
drop table BOM

Answer 2

/*
Microsoft SQL Server 2008 (RTM) - 10.0.1600.22 (Intel X86)   Jul  9 2008 14:43:34   Copyright (c) 
1988-2008 Microsoft Corporation  Enterprise Evaluation Edition on Windows NT 5.1 <X86> 
(Build 2600: Service Pack 3) 
 愿和大家共同进步
如有雷同、实属巧合
●●●●●2009-09-09 10:39:28.327●●●●●
　★★★★★soft_wsx★★★★★
*/
if OBJECTPROPERTY(object_id('tb'),'isusertable')<>0 
  drop table tb 
go
create table tb 
(ybh int, 
ebh int, 
beizhu nvarchar(20) 
) 
insert tb
select 1,0,'系统管理' 
union 
select 2,1,'用户信息' 
union 
select 3,1,'板块分类' 
union 
select 4,0,'技术部' 
union 
select 5,4,'硬件部' 
union 
select 6,4,'软件部' 
union 
select 7,5,'产品部' 
union 
select 8,5,'销售部' 
union 
select 9,6,'产品部' 
union 
select 10,6,'销售部' 
go
  
  --深度排序（模拟单编码法)
  declare @level_tt table(ybh nvarchar(1000),ebh nvarchar(1000),level int)
  declare @level int
  set @level=0
  insert @level_tt(ybh,ebh,level)
  select ybh,ybh,@level from tb where ebh=0
  while @@ROWCOUNT>0
  begin 
          set @level=@level+1
          insert @level_tt(ybh,ebh,level)
          select a.ybh,b.ebh+a.ybh,@level
            from tb a,@level_tt b
            where a.ebh=b.ybh and b.level=@level-1
 end
select space(b.level*2)+'|---->>'+a.beizhu as 结果
  from tb a,@level_tt b
  where a.ybh=b.ybh
  order by b.ebh
/*
结果 ybh ebh beizhu
---->>系统管理 1 0 系统管理
  ---->>软件部 6 4 软件部
    ---->>产品部 7 5 产品部
    ---->>销售部 8 5 销售部
    ---->>产品部 9 6 产品部
    ---->>销售部 10 6 销售部
  ---->>用户信息 2 1 用户信息
  ---->>板块分类 3 1 板块分类
---->>技术部 4 0 技术部
  ---->>硬件部 5 4 硬件部
  */
  
/*
结果
|---->>系统管理
  |---->>软件部
    |---->>产品部
    |---->>销售部
    |---->>产品部
    |---->>销售部
  |---->>用户信息
  |---->>板块分类
|---->>技术部
  |---->>硬件部
*/

Answer 3

/*
标题：查询指定节点及其所有父节点的函数
作者：爱新觉罗.毓华(十八年风雨,守得冰山雪莲花开) 
时间：2008-05-12
地点：广东深圳
*/create table tb(id varchar(3) , pid varchar(3) , name varchar(10))
insert into tb values('001' , null  , '广东省')
insert into tb values('002' , '001' , '广州市')
insert into tb values('003' , '001' , '深圳市')
insert into tb values('004' , '002' , '天河区')
insert into tb values('005' , '003' , '罗湖区')
insert into tb values('006' , '003' , '福田区')
insert into tb values('007' , '003' , '宝安区')
insert into tb values('008' , '007' , '西乡镇')
insert into tb values('009' , '007' , '龙华镇')
insert into tb values('010' , '007' , '松岗镇')
go--查询指定节点及其所有父节点的函数
create function f_pid(@id varchar(3)) returns @t_level table(id varchar(3))
as
begin
  insert into @t_level select @id
  select @id = pid from tb where id = @id and pid is not null
  while @@ROWCOUNT > 0
  begin
    insert into @t_level select @id select @id = pid from tb where id = @id and pid is not null
  end
  return
end
go--调用函数查询002(广州市)及其所有父节点
select a.* from tb a , f_pid('002') b where a.id = b.id order by a.id
/*
id   pid  name       
---- ---- ---------- 
001  NULL 广东省
002  001  广州市（所影响的行数为 2 行）
*/--调用函数查询003(深圳市)及其所有父节点
select a.* from tb a , f_pid('003') b where a.id = b.id order by a.id
/*
id   pid  name       
---- ---- ---------- 
001  NULL 广东省
003  001  深圳市（所影响的行数为 2 行）
*/--调用函数查询008(西乡镇)及其所有父节点
select a.* from tb a , f_pid('008') b where a.id = b.id order by a.id
/*
id   pid  name       
---- ---- ---------- 
001  NULL 广东省
003  001  深圳市
007  003  宝安区
008  007  西乡镇（所影响的行数为 4 行）
*/drop table tb
drop function f_pid

Answer 4

/*
Microsoft SQL Server 2008 (RTM) - 10.0.1600.22 (Intel X86)   Jul  9 2008 14:43:34   Copyright (c) 
1988-2008 Microsoft Corporation  Enterprise Evaluation Edition on Windows NT 5.1 <X86> 
(Build 2600: Service Pack 3) 
 愿和大家共同进步
如有雷同、实属巧合
●●●●●2009-09-03 17:47:36.077●●●●●
　★★★★★soft_wsx★★★★★
*/
CREATE TABLE  CS( ybh  varchar(10)  ,ebh  varchar(10),beizhu varchar(30)) INSERT INTO CS(ybh,ebh) VALUES ('2',NULL) 
INSERT INTO CS(ybh,ebh) VALUES ('1',NULL) 
INSERT INTO CS(ybh,ebh) VALUES ('100','1') 
INSERT INTO  CS(ybh,ebh) VALUES ('200','2') 
INSERT INTO  CS(ybh,ebh) VALUES ('2000','200') 
INSERT INTO  CS(ybh,ebh) VALUES (1000,100) 
INSERT INTO  CS(ybh,ebh) VALUES (20000,2000) 
INSERT INTO  CS(ybh,ebh) VALUES (2111,200) 
  
  --查的父节点（包括本身节点和所有的你节点）
 declare @level_tt table(ybh nvarchar(1000),ebh nvarchar(1000),level int)
  declare @level int
  set @level=0
  insert @level_tt(ybh,ebh,level)
  select ybh,ebh,@level from cs where ybh='20000'
  while @@ROWCOUNT>0
  begin 
          set @level=@level+1
          insert @level_tt(ybh,ebh,level)
          select a.ebh,b.ebh+a.ebh,@level
            from cs a,@level_tt b
            where a.ybh=b.ybh and b.level=@level-1
 end
select a.ybh,a.ebh
  from cs a,@level_tt b
  where a.ybh=b.ybh
  order by b.ybh desc
 
 /*
ybh ebh
20000 2000
2000 200
200 2
2 NULL
*/
  

Answer 5

if OBJECT_ID('cs')is not null
   drop table cs
if OBJECT_ID('c_f')is not null
   drop function c_f
go
CREATE TABLE  CS( ID  INT  ,PARENT_ID  INT) 
INSERT INTO CS VALUES (2,NULL) 
INSERT INTO CS VALUES (1,NULL) 
INSERT INTO CS VALUES (100,1) 
INSERT INTO  CS VALUES (200,2) 
INSERT INTO  CS VALUES (2000,200) 
INSERT INTO  CS VALUES (1000,100) 
INSERT INTO  CS VALUES (20000,2000) 
INSERT INTO  CS VALUES (2111,200) 
go
create function c_f(@id int)
returns @tb table (id int,pid int,level int)
as 
begin
declare @lev int
set @lev=1
insert into @tb select id ,PARENT_ID,@lev from cs where id=@Id
while @@rowcount>0
begin
set @lev=@lev+1
insert into @tb 
select  a.id,a.PARENT_ID,@lev from cs a 
         inner join @tb b on a.id=b.pid
         and b.level=@lev-1
end
return
end
go
select a.id,a.pid from c_f(200)  a
id          pid
----------- -----------
200         2
2           NULL(2 行受影响)

Answer 6

CREATE TABLE  CS( ID  INT  ,PARENT_ID  INT) 
INSERT INTO CS VALUES (2,NULL) 
INSERT INTO CS VALUES (1,NULL) 
INSERT INTO CS VALUES (100,1) 
INSERT INTO  CS VALUES (200,2) 
INSERT INTO  CS VALUES (2000,200) 
INSERT INTO  CS VALUES (1000,100) 
INSERT INTO  CS VALUES (20000,2000) 
INSERT INTO  CS VALUES (2111,200) create proc ps_wsp
@id int
as
with wsp
as
(
select * from  cs where id=@id
union all
select a.* from cs a,wsp b where a.id=b.parent_id
)
select * from wsp
goexec ps_wsp 200

Answer 7

/*---------------------------------
--  Author : htl258(Tony)
--  Date   : 2009-09-11 22:38:32
--  Version: Microsoft SQL Server 2008 (SP1) - 10.0.2531.0 (Intel X86) 
Mar 29 2009 10:27:29 
Copyright (c) 1988-2008 Microsoft Corporation
Enterprise Evaluation Edition on Windows NT 5.1 <X86> (Build 2600: Service Pack 2)---------------------------------*/
--> 生成测试数据表:tbIF NOT OBJECT_ID('[cs]') IS NULL
DROP TABLE cs
GO
CREATE TABLE  CS( ID  INT  ,PARENT_ID  INT) INSERT INTO CS VALUES (2,NULL) 
INSERT INTO CS VALUES (1,NULL) 
INSERT INTO CS VALUES (100,1) 
INSERT INTO  CS VALUES (200,2) 
INSERT INTO  CS VALUES (2000,200) 
INSERT INTO  CS VALUES (1000,100) 
INSERT INTO  CS VALUES (20000,2000) 
INSERT INTO  CS VALUES (2111,200) ;WITH t AS 
(
 SELECT * FROM cs WHERE ID=200
 UNION ALL
 SELECT a.* FROM cs a
JOIN t b ON a.ID=b.PARENT_ID
)
SELECT * FROM t
/*
ID          PARENT_ID
----------- -----------
200         2
2           NULL(2 行受影响)
*/

Answer 8

--> 生成测试数据表:tbIF NOT OBJECT_ID('[cs]') IS NULL
DROP TABLE cs
GO
CREATE TABLE  CS( ID  INT  ,PARENT_ID  INT) INSERT INTO CS VALUES (2,NULL) 
INSERT INTO CS VALUES (1,NULL) 
INSERT INTO CS VALUES (100,1) 
INSERT INTO  CS VALUES (200,2) 
INSERT INTO  CS VALUES (2000,200) 
INSERT INTO  CS VALUES (1000,100) 
INSERT INTO  CS VALUES (20000,2000) 
INSERT INTO  CS VALUES (2111,200) IF NOT OBJECT_ID('[fn_bom]') IS NULL
DROP FUNCTION fn_bom
GO
CREATE FUNCTION fn_bom(@id INT)
RETURNS TABLE
AS 
RETURN(
WITH t AS 
(
 SELECT * FROM cs WHERE ID=@id
 UNION ALL
 SELECT a.* FROM cs a
JOIN t b ON a.ID=b.PARENT_ID
)
SELECT * FROM t)
GOSELECT * FROM fn_bom(200)
/*
ID          PARENT_ID
----------- -----------
200         2
2           NULL(2 行受影响)
*/做成函數