① 高手幫忙：sql 里怎樣遞歸判斷父節點和子節
-- 查找所有父節點
with tab as
(
select Type_Id,ParentId,Type_Name from Sys_ParamType_V2_0 where Type_Id=316--子節點
union all
select b.Type_Id,b.ParentId,b.Type_Name
from
tab a,--子節點數據集
Sys_ParamType_V2_0 b --父節點數據集
where a.ParentId=b.Type_Id --子節點數據集.parendID=父節點數據集.ID
)
select * from tab;
-- 查找所有子節點
with tab as
(
select Type_Id,ParentId,Type_Name from Sys_ParamType_V2_0 where Type_Id=1--父節點
union all
select b.Type_Id,b.ParentId,b.Type_Name
from
tab a,--父節點數據集
Sys_ParamType_V2_0 b--子節點數據集
where b.ParentId=a.Type_Id --子節點數據集.ID=父節點數據集.parendID
)
select * from tab;
② 求高手幫忙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
③ 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
--遞歸調用，不知道是否想要這樣
④ sql根據子節點查出所有的父節點的
用函數做，根據你的表結構改：
父節點查詢子節點
create function GetChildID(@ParentID int)
returns @t table(ID int)
as
begin
insert into @t select id from table where parent_id = @ParentID
while @@rowcount<>0
begin
insert into @t select a.id from table as a
inner join @t as b
on a.parent_id = b.ID
and not exists(select 1 from @t where ID=a.ID)
end
return
end
go
子節點查詢父節點
create function GetParentID(@ChildID int)
returns @t table(PID int)
as
begin
insert into @t select parent_id from table where ID=@ChildID
while @@rowcount<>0
begin
insert into @t select a.parent_id from table as a
inner join @t as b
on a.ID=b.PID
and not exists(select 1 from @t where PID=a.parent_id)
end
return
end
go
⑤ My SQL 中怎麼遞歸查詢所有的父節點
//獲取表中所有idprotected List<Long> getIdList(String sql, Object ... params) { return xxx;} //獲取該menu下的所有子節點private List<Long> getMenuChildrenIds(long menuId) { String sql = "select menu_id from test_tb where p_id = ? "; return getIdList(sql, menuId);} public void getAllChildren(long menuId, List<Long> menuIdList) { List<Long> childrenIds = getMenuChildrenIds(menuId); for (long menu_Id : childrenIds) { menuIdList.add(menu_Id); //計數 int count = geliDao.count("select count(1) from test_tb where p_id = ? ", menu_Id, status); if (count > 0) { getAllChildren(menu_Id, menuIdList); } }} //public List<Menu> getMenuChildren(long menuId) { return orm.list(Menu.class, getMenuChildrenIds(menuId ).toArray()); } //執行,全找出來menuIds，放到list裡面List<Long> menuIds = new ArrayList<Long>();menuIds.add(menu.getMenuId());getAllChildren(menu.getMenuId(), menuIds);
⑥ 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'
這個只是簡單的按你給的數據寫的，實際中可能存在問題，，，，
⑦ SQL遞歸獲取所有父節點的函數
*************
函數如下，請根據你自己的實際情況，把漢字換成相應的具體信息
*************
CREATEFUNCTION函數名(@idASvarchar(2))RETURNSvarchar(1000)AS
BEGIN
DECLARE@tmpVARCHAR(1000)
IF@idISNOTNULL
BEGIN
SELECT@tmp=@id+','+isnull(dbo.函數名(第三列欄位),'')FROM[表名]WHERE[第一列欄位]=@id
RETURN@tmp
***********
調用方法：
***********
SELECTsubstring(dbo.函數名('15'),1,len(dbo.函數名('15'))-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
⑨ SQL同時查詢子、父節點的值
直接查就行了，也可以試一下下面的：
select name,value,(select ParentValue from b) as ParentValue from a where OrgID = '001' ;