Ⅰ 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
Ⅱ 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写法：树节点放一个表中，怎么用一条语句查询一个节点及对应的所有父节点信息。
建议使用递归，
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 （根据子节点查询父节点信息）
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语句查询出父节点下的所有子节点
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 里怎样递归判断父节点和子节
-- 查找所有父节点
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;