sql查詢父節點

1. sql （根據子節點查詢父節點信息）

declare @lt table(id int,level int)
declare @level int
declare @findid int
--初始化數據
set @findid = 25/*找nodeId = 25*/
--end of 初始化數據

set @level = 1
insert @lt select @findid,@level
while @@rowcount > 0
begin
set @level = @level + 1
insert @lt select a. parentId,@level
from 你的表名 a, @lt b
where a.nodeId = b.nodeId and b.level = @level - 1
end
--連接得到結果
select a.*
from 你的表名 a inner join @lt b
on a.nodeId=b.nodeId

2. 求高手幫忙sql寫法：樹節點放一個表中，怎麼用一條語句查詢一個節點及對應的所有父節點信息。

建議使用遞歸，
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

3. SQL語句查詢出一個父節點下的所有子節點

假如你的表名字是tt，創建下面的存儲過程，使用存儲過程即可查詢。

create proc querytree
@user varchar(100)
as
begin
declare @id int
declare @T_tmp table(id int,username varchar(100),parentid int)
insert into @T_tmp select * from tt where username=@user
while(@@rowcount>0)
begin
insert into @T_tmp select * from tt where parentid in (select id from @T_tmp) and id not in(select id from @T_tmp)
end
select * from @T_tmp where username<>@user
end

4. SQL同時查詢當前節點、父節點、根節點的值

SELECTt1.name,t1.VALUE,t2.VALUEASparentvalue,(SELECTCASEWHENISNULL(parentid,'')=''THENa.VALUEEND
FROMbLEFTJOINdbo.aONa.orgid=b.orgid
WHEREISNULL(parentid,'')='')ASRootValue
FROMbLEFTJOINat1ONt1.orgid=b.orgid
LEFTJOINat2ONt2.orgid=b.parentid
WHEREb.orgid='001'

這個只是簡單的按你給的數據寫的，實際中可能存在問題，，，，

5. SQL語句查詢出父節點下的所有子節點

createtable##tmp_users(idint,usernamenvarchar(255),parentidint)
declare@IDint
select@ID=idfromt_Userstwhereexists
(select*fromt_Userst2wheret2.id=t.parentidandt2.username='user1')
execAddSons@ID
select*from##tmp_users
droptable##tmp_users


--存儲
createprocereAddSons@idint
as
ifexists(select*fromt_Userswhereparentid=@id)
begin
declare@tmp_IDint
declarecurcursorfor
selectidfromt_Userswhereparentid=@id
opencur
fetchnextfromcurinto@tmp_ID
while@@FETCH_STATUS=0
begin
insertinto##tmp_users
select*fromt_Userstwhereid=@tmp_ID
ifexists(select*fromt_Userswhereparentid=@tmp_ID)
begin
execAddSons@tmp_ID
end
fetchnextfromcurinto@tmp_ID
end
closecur
DEALLOCATEcur
end
--遞歸調用，不知道是否想要這樣