关于网友提出的“如何更改apple id 如何通过一个树根ID查找到它所有子叶的ID（SQL问题）”问题疑问，本网通过在网上对“如何更改apple id 如何通过一个树根ID查找到它所有子叶的ID（SQL问题）”有关的相关答案进行了整理，供用户进行参考，详细问题解答如下：
问题：如何更改apple id 如何通过一个树根ID查找到它所有子叶的ID（SQL问题）描述:
数据结构如下：
ID PID
1 0
2 1
3 1
4 2
5 0
6 3
1.查询祖先ID为1的所有ID。
2.然后按照PID为0的条件分组。
3.通过一个ID查找到它的祖先ID
另一帖子：
http://topic.csdn.net/u/20071112/15/1e1b7d22-a0bd-4ce8-a23f-b675f92dd5ca.html
解决方案1:
楼上几位太强了...都这么详细了....
我只有路过的份了....,不过还是想多嘴一下,楼主可以在代码里用递归取出来....
用存储过程递归
解决方案4:
2。
create table BOM(ID INT,PID INT)
insert into BOM select 1,0
insert into BOM select 2,1
insert into BOM select 3,1
insert into BOM select 4,2
insert into BOM select 5,0
insert into BOM select 6,3
create proc wsp
as
declare @ID int
declare @PID int
declare @temp table(id int,pid int)
insert into @temp select * from BOM where pid=0--根节点
declare cur cursor for select * from BOM where PID in(select ID from @temp)
open cur
fetch next from cur into @ID,@PID
while(@@fetch_status=0)
begin
insert into @temp values (@ID,@PID)
if exists(select * from BOM where PID=@ID)
begin
insert into @temp select * from BOM where PID=@ID
end
fetch next from cur into @ID,@PID
end
close cur
deallocate cur
select distinct * from @temp order by pid
exec wsp
参考下面的SQL语句
没明白"然后按照PID为0的条件分组"是什么意思,所以这个功能没有做
解决方案5:
--创建测试表
CREATE TABLE TableName
(
[ID] INT PRIMARY KEY NOT NULL,
[PID] INT NOT NULL,
[Name] NVARCHAR(50)
)
--插入测试数据
INSERT INTO TableName([ID],[PID]) VALUES(1,0)
INSERT INTO TableName([ID],[PID]) VALUES(2,1)
INSERT INTO TableName([ID],[PID]) VALUES(3,1)
INSERT INTO TableName([ID],[PID]) VALUES(4,2)
INSERT INTO TableName([ID],[PID]) VALUES(5,0)
INSERT INTO TableName([ID],[PID]) VALUES(6,3)
GO
--创建存储过程
CREATE PROCEDURE dbo.GetAllChildID
@PID INT
AS
IF @PID IS NULL RETURN
DECLARE @OldRecordCnt INT
DECLARE @CurRecordCnt INT
DECLARE @tmp Table ([ID] INT PRIMARY KEY NOT NULL)
INSERT INTO @tmp VALUES(@PID)
SET @OldRecordCnt=1
WHILE 1=1
BEGIN
INSERT INTO @tmp SELECT a.[ID] FROM TableName a INNER JOIN @tmp b ON a.[PID]=b.[ID]
WHERE a.[ID] NOT IN (SELECT [ID] FROM @tmp)
SELECT @CurRecordCnt=COUNT(*) FROM @tmp
IF @CurRecordCnt=@OldRecordCnt
BREAK
ELSE
SET @OldRecordCnt=@CurRecordCnt
END
DELETE FROM @tmp WHERE [ID]=@PID
SELECT * FROM @tmp
GO
CREATE PROCEDURE dbo.GetEndParentID
@ID INT,
@PID INT OUTPUT
AS
SET @PID=@ID
DECLARE @TmpID INT
SET @TmpID=-1
WHILE (@TmpID=-1) OR (@TmpID<>0)
BEGIN
SELECT @TmpID=PID FROM TableName WHERE [ID]=@PID
IF @TmpID=-1
BEGIN
SET @PID=NULL
BREAK
END
IF (@TmpID<>0) SET @PID=@TmpID
END
GO
--查询测试
--1.查询祖先ID为1的所有ID。
DECLARE @PID INT
SET @PID=1
EXEC dbo.GetAllChildID @PID
/*
--测试结果
ID
----------------------------------------------------
2
3
4
6
*/
--3.通过一个ID查找到它的祖先ID
DECLARE @ID INT
SET @ID=6
EXEC dbo.GetEndParentID @ID,@PID OUTPUT
SELECT @PID
/*
--测试结果
-------------
1
*/
--删除测试表和存储过程
DROP TABLE TableName
DROP PROCEDURE dbo.GetAllChildID
DROP PROCEDURE dbo.GetEndParentID
select id from table where pid=1;
select * from table where pid=id;