楼主表达的是意思是:如果A表当前记录有父节点,就找出父节点记录对应的B表name,以"/"方式,添加到左边,以此类推? SET NOCOUNT ON DECLARE @A TABLE(id INT, userid INT, nodeid INT, pid INT, Name VARCHAR(1000)) DECLARE @B TABLE(id INT, name VARCHAR(10))INSERT INTO @A SELECT 1, 1, 1, 0, '' UNION ALL SELECT 2, 1, 3, 2, '' UNION ALL SELECT 3, 1, 2, 4, '' UNION ALL SELECT 4, 1, 5, 4, '' UNION ALL SELECT 5, 2, 6, 7, '' UNION ALL SELECT 6, 2, 3, 5, '' UNION ALL SELECT 7, 2, 6, 3, '' INSERT INTO @B SELECT 1, 'a' UNION ALL SELECT 2, 'b' UNION ALL SELECT 3, 'c' UNION ALL SELECT 4, 'd' UNION ALL SELECT 5, 'e' UNION ALL SELECT 6, 'f' UNION ALL SELECT 7, 'g'SELECT * FROM @A SELECT * FROM @B--记录A表当前id, 起始id,结束id, DECLARE @TemAid INT,@MinAid INT, @MaxAid INT--中间变量 DECLARE @Aid INT, @TemApid INT, @StrName VARCHAR(1000)--记录行数,来判断A表父子关系是否会死循环 DECLARE @RowCount INT, @TemRowCount INTSELECT @MinAid = MIN(id), @MaxAid = MAX(id), @RowCount = COUNT(*) FROM @A SELECT @TemAid = @MinAid WHILE(@TemAid <= @MaxAid) BEGIN SELECT @Aid = @TemAid, @TemApid = 0, @StrName = '', @TemRowCount = @RowCount WHILE EXISTS(SELECT * FROM @A AS A INNER JOIN @B AS B ON A.nodeid = B.id WHERE A.id = @Aid) BEGIN SELECT @StrName = B.name + '/' + @StrName, @Aid = A.pid FROM @A AS A INNER JOIN @B AS B ON A.nodeid = B.id WHERE A.id = @Aid SELECT @TemRowCount = @TemRowCount - 1 IF(@TemRowCount < 0) BEGIN SELECT @StrName = 'A表父子关系乱伦/' BREAK END END
UPDATE @A SET Name = CASE @StrName WHEN '' THEN '' ELSE LEFT(@StrName, LEN(@StrName) - 1) END WHERE id = @TemAid
SELECT @TemAid = @TemAid + 1 ENDSELECT * FROM @ASET NOCOUNT ON
SET NOCOUNT ON
DECLARE @A TABLE(id INT, userid INT, nodeid INT, pid INT, Name VARCHAR(1000))
DECLARE @B TABLE(id INT, name VARCHAR(10))INSERT INTO @A
SELECT 1, 1, 1, 0, '' UNION ALL
SELECT 2, 1, 3, 2, '' UNION ALL
SELECT 3, 1, 2, 4, '' UNION ALL
SELECT 4, 1, 5, 4, '' UNION ALL
SELECT 5, 2, 6, 7, '' UNION ALL
SELECT 6, 2, 3, 5, '' UNION ALL
SELECT 7, 2, 6, 3, '' INSERT INTO @B
SELECT 1, 'a' UNION ALL
SELECT 2, 'b' UNION ALL
SELECT 3, 'c' UNION ALL
SELECT 4, 'd' UNION ALL
SELECT 5, 'e' UNION ALL
SELECT 6, 'f' UNION ALL
SELECT 7, 'g'SELECT * FROM @A
SELECT * FROM @B--记录A表当前id, 起始id,结束id,
DECLARE @TemAid INT,@MinAid INT, @MaxAid INT--中间变量
DECLARE @Aid INT, @TemApid INT, @StrName VARCHAR(1000)--记录行数,来判断A表父子关系是否会死循环
DECLARE @RowCount INT, @TemRowCount INTSELECT @MinAid = MIN(id), @MaxAid = MAX(id), @RowCount = COUNT(*) FROM @A
SELECT @TemAid = @MinAid
WHILE(@TemAid <= @MaxAid)
BEGIN
SELECT @Aid = @TemAid, @TemApid = 0, @StrName = '', @TemRowCount = @RowCount
WHILE EXISTS(SELECT * FROM @A AS A INNER JOIN @B AS B ON A.nodeid = B.id WHERE A.id = @Aid)
BEGIN
SELECT @StrName = B.name + '/' + @StrName, @Aid = A.pid FROM @A AS A INNER JOIN @B AS B ON A.nodeid = B.id WHERE A.id = @Aid
SELECT @TemRowCount = @TemRowCount - 1
IF(@TemRowCount < 0)
BEGIN
SELECT @StrName = 'A表父子关系乱伦/'
BREAK
END
END
UPDATE @A SET Name = CASE @StrName WHEN '' THEN '' ELSE LEFT(@StrName, LEN(@StrName) - 1) END WHERE id = @TemAid
SELECT @TemAid = @TemAid + 1
ENDSELECT * FROM @ASET NOCOUNT ON