34,593
社区成员
发帖
与我相关
我的任务
分享
USE tempdb
GO
IF OBJECT_ID('testTable') IS NOT NULL
DROP TABLE testTable
GO
CREATE TABLE testTable(id INT PRIMARY KEY,[name] NVARCHAR(20),parentId INT)
INSERT INTO testTable(id,[name],parentId) VALUES(1,'xf1',0)
INSERT INTO testTable(id,[name],parentId) VALUES(2,'xf2',0)
INSERT INTO testTable(id,[name],parentId) VALUES(3,'xf3',2)
INSERT INTO testTable(id,[name],parentId) VALUES(4,'xf4',3)
INSERT INTO testTable(id,[name],parentId) VALUES(5,'xf5',4)
INSERT INTO testTable(id,[name],parentId) VALUES(6,'xf6',5)
--1. 不用 with
DECLARE @t TABLE (id INT,[name] NVARCHAR(20),parentId INT)
INSERT INTO @t
SELECT * FROM testTable AS tt WHERE id=6
WHILE EXISTS(
SELECT 1 FROM testTable AS a INNER JOIN @t b ON a.id=b.parentId
AND a.id NOT IN (SELECT id FROM @t)
)
BEGIN
INSERT INTO @t
SELECT a.* FROM testTable AS a INNER JOIN @t b ON a.id=b.parentId
AND a.id NOT IN (SELECT id FROM @t)
END
SELECT * FROM @t
--2. 用 with
;WITH cte AS(
SELECT * FROM testTable WHERE id='6'
UNION ALL
SELECT a.* FROM testTable AS a INNER JOIN
cte ON a.id=cte.parentId
)
SELECT * FROM cte
declare @TestTable table (id int, name char(3), ParentID int);
insert into @TestTable values
(1, 'xf1', 0), (2, 'xf2', 0), (3, 'xf3', 2),
(4, 'xf4', 3), (5, 'xf5', 4), (6, 'xf6', 5);
declare @ResultTable table (id int, name char(3), ParentID int);
declare @id int = null, @name char(3) ='xf6', @ParentID int;
select @id=id, @name=name, @ParentID=ParentID from @TestTable where name='xf6';
while @id is not null
begin
insert into @ResultTable values (@id, @name, @ParentID);
set @id = null;
select @id=id, @name=name, @ParentID=ParentID from @TestTable where id=@ParentID;
end;
select * from @ResultTable order by id;
id name ParentID
----------- ---- -----------
2 xf2 0
3 xf3 2
4 xf4 3
5 xf5 4
6 xf6 5
--测试数据
IF OBJECT_ID('#tab') IS NOT NULL
DROP TABLE #tab
CREATE TABLE #tab(
id INT ,
NAME VARCHAR(20),
parentID INT
)
INSERT INTO #tab
SELECT 1,'Xf1',0
UNION ALL
SELECT 2,'Xf2',0
UNION ALL
SELECT 3,'Xf3',2
UNION ALL
SELECT 4,'Xf4',3
UNION ALL
SELECT 5,'Xf5',4
UNION ALL
SELECT 6,'Xf6',5
UNION ALL
SELECT 7,'Xf7',6
--测试数据结束
SELECT a.* FROM #tab a
INNER JOIN #tab b ON a.id=b.parentID
WHERE b.parentID<=(SELECT parentID FROM #tab WHERE NAME='Xf6')
UNION
SELECT b.* FROM #tab a
INNER JOIN #tab b ON a.id=b.parentID
WHERE b.parentID<=(SELECT parentID FROM #tab WHERE NAME='Xf6')
id NAME parentID
----------- -------------------- -----------
2 Xf2 0
3 Xf3 2
4 Xf4 3
5 Xf5 4
6 Xf6 5
(5 行受影响)
上面是手机上回复的,看着太乱了重新用电脑给你整了下