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

2024-12-25 17:47:45
推荐回答(4个)
回答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

回答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

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

回答3:

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

回答4:

中华健康的绿