现在有一个表
AID NAME PARENTID
1 A 0
2 A1 1
3 A2 1
4 B 0
5 B1 4
6 A11 2
7 A111 6
8 B11 5
9 A1111 7
10 A11111 9
这是一个动态树的实现现在我想传入一个ID得到他所有父ID的名称如我传入 2 显示 A
传入9 显示 A111,A11,A1,A
传入 8 显示 B1 B这样写只可以根据当前的节点获取父节点的名称
SELECT NAME FROM A WHERE PARENTID = (SELECT PARENTID FROM A WHERE ID = @id)如果想要传入一个ID获取到它的所有父NAME的SQL应该怎么写?
AID NAME PARENTID
1 A 0
2 A1 1
3 A2 1
4 B 0
5 B1 4
6 A11 2
7 A111 6
8 B11 5
9 A1111 7
10 A11111 9
这是一个动态树的实现现在我想传入一个ID得到他所有父ID的名称如我传入 2 显示 A
传入9 显示 A111,A11,A1,A
传入 8 显示 B1 B这样写只可以根据当前的节点获取父节点的名称
SELECT NAME FROM A WHERE PARENTID = (SELECT PARENTID FROM A WHERE ID = @id)如果想要传入一个ID获取到它的所有父NAME的SQL应该怎么写?
看看邹老大的树形处理
http://blog.csdn.net/zjcxc/category/125593.aspx
declare @tb table([ID] int,[NAME] varchar(6),[PARENTID] int)
insert @tb
select 1,'A',0 union all
select 2,'A1',1 union all
select 3,'A2',1 union all
select 4,'B',0 union all
select 5,'B1',4 union all
select 6,'A11',2 union all
select 7,'A111',6 union all
select 8,'B11',5 union all
select 9,'A1111',7 union all
select 10,'A11111',9select * from @tb
declare @id int
set @Id=9
declare @s varchar(50)
;
with cte1 as
(
select id,name,[PARENTID] from @tb where id=@id
union all
select t.id,t.name,t.[PARENTID] from cte1 c join @tb t on c.[PARENTID]=t.id
)
select @s=isnull(@s+',','')+name from cte1 where [PARENTID]<>0select @s/*--------------------------------------------------
A1111,A111,A11,A1(1 行受影响)*/
create table Info
(
ID char(3),
PID char(3),
[Name] varchar(20)
)
insert into Info 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','小分市'
create function dbo.f_id(@id char(3))
returns @tb_level table(id char(3),[level] int)
as
begin
declare @level int
set @level=0
insert into @tb_level select @id,@level
while @@rowcount>0
begin
set @level=@level+1;
insert into @tb_level select I.ID,@level from Info I,@tb_level tb where I.PID=tb.ID and [level]=@level-1
end
return
end
select I.* from Info I,dbo.f_id('002') F where I.ID=F.ID
ID PID Name
---- ---- --------------------
002 001 烟台市
004 002 招远市
(2 行受影响)
本文来自CSDN博客,转载请标明出处:http://blog.csdn.net/ws_hgo/archive/2009/07/24/4377428.aspx
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-16 18:06:02.983●●●●●
★★★★★soft_wsx★★★★★
*/
if OBJECT_ID('tb') is not null drop table tb
go
create table tb(ybh nvarchar(10),beizhu nvarchar(1000),ebh nvarchar(10))
insert tb
select
1, 'A', 0 union all select
2, 'A1', 1 union all select
3, 'A2' , 1 union all select
4, 'B' , 0 union all select
5, 'B1' , 4 union all select
6, 'A11' , 2 union all select
7, 'A111' , 6 union all select
8, 'B11' , 5 union all select
9, 'A1111' , 7 union all select
10, 'A11111' , 9
/*
(无列名) ybh ebh beizhu ybh ebh level
----五华区 0005 0002 五华区 0005 0005 0
----西园路192号 0006 0005 西园路192号 0006 00050006 1
----金色梧桐 0010 0006 金色梧桐 0010 000500060010 2
----科技有限公司 0011 0010 科技有限公司 0011 0005000600100011 3
*/
if object_id('f_tb') is not null drop function f_tb
go
create function f_tb(@fid int)
returns nvarchar(4000)
as
begin
declare @sql nvarchar(4000)
set @sql=''
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 tb where ybh=@fid
while @@ROWCOUNT>0
begin
set @level=@level+1
insert @level_tt(ybh,ebh,level)
select a.ebh,b.ebh+a.ebh,@level
from tb a,@level_tt b
where a.ybh=b.ybh and b.level=@level-1
endselect @sql=@sql+','+a.beizhu
from tb a,@level_tt b
where a.ybh=b.ybh and b.level>0
order by b.ebh
return(stuff(@sql,1,1,N''))
endselect dbo.f_tb(2),dbo.f_tb(9),dbo.f_tb(8)
/*
结果
(无列名) (无列名) (无列名)
A A111,A11,A1,A B1,B
*/好了!
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-16 18:06:02.983●●●●●
★★★★★soft_wsx★★★★★
*/
if OBJECT_ID('tb') is not null drop table tb
go
create table tb(ybh nvarchar(10),beizhu nvarchar(1000),ebh nvarchar(10))
insert tb
select
1, 'A', 0 union all select
2, 'A1', 1 union all select
3, 'A2' , 1 union all select
4, 'B' , 0 union all select
5, 'B1' , 4 union all select
6, 'A11' , 2 union all select
7, 'A111' , 6 union all select
8, 'B11' , 5 union all select
9, 'A1111' , 7 union all select
10, 'A11111' , 9
if object_id('f_tb') is not null drop function f_tb
go
create function f_tb(@fid int)
returns nvarchar(4000)
as
begin
declare @sql nvarchar(4000)
set @sql=''
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 tb where ybh=@fid
while @@ROWCOUNT>0
begin
set @level=@level+1
insert @level_tt(ybh,ebh,level)
select a.ebh,b.ebh+a.ebh,@level
from tb a,@level_tt b
where a.ybh=b.ybh and b.level=@level-1
endselect @sql=@sql+','+a.beizhu
from tb a,@level_tt b
where a.ybh=b.ybh and b.level>0
order by b.ebh
return(stuff(@sql,1,1,N''))
endselect dbo.f_tb(2),dbo.f_tb(9),dbo.f_tb(8)
/*
结果
(无列名) (无列名) (无列名)
A A111,A11,A1,A B1,B
*/不好意思!刚才多点了数据!
if object_id('tb')is not null drop table tb
go
create table tb(ID int, NAME varchar(10),PARENTID int)
insert tb select
1, 'A' ,0 union all select
2 , 'A1' ,1union all select
3 , 'A2' ,1union all select
4 , 'B' ,0union all select
5 , 'B1' ,4union all select
6 , 'A11' , 2union all select
7 , 'A111' , 6union all select
8 , 'B11' , 5union all select
9 ,'A1111' , 7union all select
10 ,'A11111', 9if object_id('f_str')is not null drop function f_str
go
create function f_str(@id int)
returns varchar(20)
as
begin
declare @str varchar(20)
set @str='' while exists(select 1 from tb where id=@id)
begin
set @str=@str+','+(select name from tb where id=@id)
set @id=(select PARENTID from tb where id=@id)
end
return stuff(@str,1,1,'')
end
go declare @id int
set @id=9select dbo.f_str(@id) from tb where id=@id--------------------
A1111,A111,A11,A1,A(1 行受影响)
/*---------------------------------
-- Author : htl258(Tony)
-- Date : 2009-09-16 18:08:58
-- 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('[tb]') IS NULL
DROP TABLE [tb]
GO
CREATE TABLE [tb]([ID] INT,[NAME] NVARCHAR(10),[PARENTID] INT)
INSERT [tb]
SELECT 1,'A',0 UNION ALL
SELECT 2,'A1',1 UNION ALL
SELECT 3,'A2',1 UNION ALL
SELECT 4,'B',0 UNION ALL
SELECT 5,'B1',4 UNION ALL
SELECT 6,'A11',2 UNION ALL
SELECT 7,'A111',6 UNION ALL
SELECT 8,'B11',5 UNION ALL
SELECT 9,'A1111',7 UNION ALL
SELECT 10,'A11111',9
GO
--SELECT * FROM [tb]-->SQL查询如下:
IF OBJECT_ID('FN_STR') IS NOT NULL
DROP FUNCTION FN_STR
GO
CREATE FUNCTION FN_STR(@id INT)
RETURNS NVARCHAR(100)
AS
BEGIN
DECLARE @s NVARCHAR(100);
WITH t AS
(
SELECT * FROM tb WHERE id=@id
UNION ALL
SELECT a.* FROM tb a JOIN t b ON a.[ID]=b.[PARENTID]
)
SELECT @s=STUFF((SELECT ','+NAME FROM T WHERE id<>@id FOR XML PATH('')),1,1,'')
RETURN @s
END
GOSELECT dbo.FN_STR(8)
/*
----------------------------------------------------------------------------------------------------
B1,B(1 行受影响)
*/
declare @tb table([ID] int,[NAME] varchar(6),[PARENTID] int)
insert @tb
select 1,'A',0 union all
select 2,'A1',1 union all
select 3,'A2',1 union all
select 4,'B',0 union all
select 5,'B1',4 union all
select 6,'A11',2 union all
select 7,'A111',6 union all
select 8,'B11',5 union all
select 9,'A1111',7 union all
select 10,'A11111',9declare @pid int,@id int,@i_input int
set @i_input = 8
set @pid = 0
select @id = parentid from @tb where id = @i_inputdeclare @name varchar(20)
set @name = ''while(@@ROWCOUNT > 0)
begin
update @tb set
@name = case when @name ='' then '' else @name+','end + name
,@id = parentid
from @tb
where id = @id
endselect @name--------------------
B1,B
if OBJECT_ID('tb') is not null drop table tb
go
create table tb(id int,sname nvarchar(1000),pid int)
insert tb
select
1, 'A', 0 union all select
2, 'A1', 1 union all select
3, 'A2' , 1 union all select
4, 'B' , 0 union all select
5, 'B1' , 4 union all select
6, 'A11' , 2 union all select
7, 'A111' , 6 union all select
8, 'B11' , 5 union all select
9, 'A1111' , 7 union all select
10, 'A11111' , 9declare @str varchar(4000)
declare @id int
set @id=9
select @str='',@id=pid from tb where id=@id
while @@rowcount>0
select @str=@str+','+sname,@id=pid from tb where id=@id
select stuff(@str,1,1,'')
标题:查询各节点的父路径函数
作者:爱新觉罗.毓华(十八年风雨,守得冰山雪莲花开)
时间:2008-05-12
地点:广东深圳
*//*
原始数据及要求结果如下:
--食品
--水果
--香蕉
--苹果
--蔬菜
--青菜
id pid name
----------- ----------- --------------------
1 0 食品
2 1 水果
3 1 蔬菜
4 2 香蕉
5 2 苹果
6 3 青菜要求得到各节点的父路径即如下结果:
id pid name 路径
--- --- ----- ---------------
1 0 食品 食品
2 1 水果 食品,水果
3 1 蔬菜 食品,蔬菜
4 2 香蕉 食品,水果,香蕉
5 2 苹果 食品,水果,苹果
6 3 青菜 食品,蔬菜,青菜
*/create table tb (id int , pid int , name nvarchar(20))
insert into tb values(1 , 0 , '食品')
insert into tb values(2 , 1 , '水果')
insert into tb values(3 , 1 , '蔬菜')
insert into tb values(4 , 2 , '香蕉')
insert into tb values(5 , 2 , '苹果')
insert into tb values(6 , 3 , '青菜')
go--查询各节点的父路径函数
create function f_pid(@id int) returns varchar(100)
as
begin
declare @re_str as varchar(100)
set @re_str = ''
select @re_str = name from tb where id = @id
while exists (select 1 from tb where id = @id and pid <> 0)
begin
select @id = b.id , @re_str = b.name + ',' + @re_str from tb a , tb b where a.id = @id and a.pid = b.id
end
return @re_str
end
goselect * , dbo.f_pid(id) 路径 from tb order by iddrop table tb
drop function f_pid
if object_id('A') is not null
drop table A
Go
create table A(ID int , Name varchar(10) , parentid int)
Go
insert into A
select 1 ,'A' , 0 union all
select 2 ,'A1' , 1 union all
select 3 , 'A2', 1 union all
select 4 , 'B' , 0 union all
select 5 , 'B1', 4 union all
select 6 , 'A11' , 2 union all
select 7 , 'A111' , 6 union all
select 8 , 'B11' , 5 union all
select 9 , 'A1111', 7 union all
select 10 , 'A11111', 9with
ta as
(
select * from A where ID = (select parentid from A where id = 9)
union all
select b.* from A b , ta c
where b.ID = c.parentid
)
select * from ta
=============================================
ID Name Parentid
----------------------------------
7 A111 6
6 A11 2
2 A1 1
1 A 0
create table mytree
(
id int identity(1,1) primary key,
name varchar(8),
parentId int
)
go
--测试数据
insert into mytree
select 'a',0 union all
select 'a1',1 union all
select 'a2',1 union all
select 'b',0 union all
select 'b1',4 union all
select 'a11',2 union all
select 'a111',6 union all
select 'b11',5 union all
select 'a1111',7 union all
select 'a11111',9
go--自定义函数
create function GetNames(@parm int)
returns @tt table (col varchar(8),level int)
as
begin
declare @mylevel int --用于存储层数
declare @pid int --临时用于存储父编号
declare @name varchar(8) --用于存储父名称
set @mylevel=0;
select @pid=parentid from mytree where id=@parm
--insert into @tt values(@pid,'')
while(@pid<>0)
begin
select @name=name from mytree where id=@pid
insert into @tt values(@name,@mylevel)
select @mylevel=@mylevel+1;
select @pid=parentid from mytree where id=@pid;
end
return
endselect * from GetNames(9) order by level desc