Question

求高手帮忙sql写法：树节点放一个表中，怎么用一条语句查询一个节点及对应的所有父节点信息。

表结构和jyl_19给出的就可。我要的是一个子节点对应的父节点信息和它显示在一行里。
类似于：select * from table1 t1 left outer join table1 t2 on t1.pid=t2.id left ......知道t2.pid=0结束。这样的结果。我想用存储过程，但不知道在存储过程里怎么执行一个存放语句的varchar变量并把结果给另一个变量。如：@sql varchar(5000),@co int;set @co=--执行@sql的结果。

Answer 1

建议使用递归，
oracl语法示例如下、
CREATE TABLE TBL_TEST
(
ID NUMBER, --主键
NAME VARCHAR2(100 BYTE),
PID NUMBER DEFAULT 0 --------父节点主键
);
插入测试数据：
INSERT INTO TBL_TEST(ID,NAME,PID) VALUES('1','10','0');
INSERT INTO TBL_TEST(ID,NAME,PID) VALUES('2','11','1');
INSERT INTO TBL_TEST(ID,NAME,PID) VALUES('3','20','0');
INSERT INTO TBL_TEST(ID,NAME,PID) VALUES('4','12','1');
INSERT INTO TBL_TEST(ID,NAME,PID) VALUES('5','121','2');
从Root往树末梢递归
select * from TBL_TEST
start with id=1
connect by prior id = pid
从末梢往树ROOT递归
select * from TBL_TEST
start with id=5
connect by prior pid = id
SQL server 2005语法示例如下、
CREATE TABLE TBL_TEST
(
ID int,
NAME VARCHAR(100),
PID int DEFAULT 0
);

　　插入测试数据：

INSERT INTO TBL_TEST(ID,NAME,PID) VALUES('1','10','0');
INSERT INTO TBL_TEST(ID,NAME,PID) VALUES('2','11','1');
INSERT INTO TBL_TEST(ID,NAME,PID) VALUES('3','20','0');
INSERT INTO TBL_TEST(ID,NAME,PID) VALUES('4','12','1');
INSERT INTO TBL_TEST(ID,NAME,PID) VALUES('5','121','2');

select * from TBL_TEST

　--　从Root往树末梢递归

with cte as
(select *,0 as TLevel from TBL_TEST where ID=1
union all
select t1.*,t2.TLevel+1 from TBL_TEST t1 inner join cte t2 on t1.PID=t2.ID)
select * from cte

　--　从末梢往树ROOT递归

with cte as
(select *,0 as TLevel from TBL_TEST where ID=5
union all
select t1.*,t2.TLevel+1 from TBL_TEST t1 inner join cte t2 on t1.ID=t2.PID)
select * from cte

追问

这种方法也可以查出结果集，不过是针对一条生成了列表，我需要的是这些显示为一行，也就是类似left outer join ,把所有父级信息显示在此行的后面！ 很感谢~！

追答

如果只找一级父节点，可以直接left join
select * from table1 t1 left outer join table1 t2 on t1.pid=t2.id
传参数可以用sp_executesql,实例传回id=2的父节点、
declare @str nvarchar(1000)
declare @co int
set @str='select @i=t2.id from TBL_TEST t1 left outer join TBL_TEST t2 on t1.pid=t2.id where t1.id=2'
exec sp_executesql @str,N'@i int out',@i=@co out
select @co

追问

不是一级，不确定是多少级。

追答

适用于sqlserver2005以上环境
with cte as
(select *,ID as Seed from TBL_TEST where ID=5
union all
select t1.*,t2.Seed from TBL_TEST t1 inner join cte t2 on t1.ID=t2.PID)
select parent=cast((select cast(PID as varchar)+',' from cte for xml path('')) as varchar)

Answer 2

--例如有表Table，其结构及记录如下:
Id Pid Name
1 0 A
2 1 B
3 2 C
4 1 D
5 0 E
6 5 F
……

--在SQL中定义一个函数: [GET_CHILD]

CREATE FUNCTION [GET_CHILD]
(
@ID VARCHAR(14)
)
RETURNS @T_LEVEL
TABLE(ID VARCHAR(14),
LEVEL INT)
WITH ENCRYPTION --加密存储过程
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 Table A
,@T_LEVEL B
WHERE A.PID = B.ID
AND B.LEVEL = @LEVEL - 1
END
RETURN

--调用该函数得到结果的SQL语句:

SELECT A.*
FROM TB A, GET_CHILD('002') B
WHERE A.ID = B.ID

看看这样是否可以通过一个子节点把它的所有父节点列出来？

Answer 3

你的表结构是什么样子的，是包含 子节点 和父节点两个字段吗？

追问

表结构和jyl_19给出的就可。我要的是一个子节点对应的父节点信息和它显示在一行里。
类似于：select * from table1 t1 left outer join table1 t2 on t1.pid=t2.id left ......知道t2.pid=0结束。这样的结果。

Answer 4

中华健康的绿