递归查树形结构

--drop table tb
--go
--create table tb(code int,su_code int)
--insert into tb(code,su_code) values(1,null)
--insert into tb(code,su_code) values(11,1)
--insert into tb(code,su_code) values(12,1)
--insert into tb(code,su_code) values(111,11)
--insert into tb(code,su_code) values(112,11)
--insert into tb(code,su_code) values(121,12)
--insert into tb(code,su_code) values(122,12)
--insert into tb(code,su_code) values(1221,122)

--SELECT * FROM tb
--SELECT * FROM #stack
--DROP TABLE #stack

go

--create table #stack(item int,levels int)
--delete from #stack
SET nocount ON
go
DECLARE @top INT
DECLARE @level INT
DECLARE @line VARCHAR(128)
SELECT @top = code
FROM tb
WHERE su_code IS NULL
INSERT INTO #stack
VALUES ( @top, 1 )
SELECT @level = 1
WHILE @level > 0
BEGIN
IF EXISTS ( SELECT *
FROM #stack
WHERE levels = @level )
BEGIN
SELECT @top = item
FROM #stack
WHERE levels = @level
SELECT @line = SPACE(@level - 1) + CONVERT(VARCHAR, @top)
PRINT @line
DELETE FROM #stack
WHERE levels = @level
AND item = @top
INSERT INTO #stack
SELECT code ,
@level + 1
FROM tb
WHERE su_code = @top
IF @@rowcount > 0
SELECT @level = @level + 1
END
ELSE
SELECT @level = @level - 1
END

转载于:https://www.cnblogs.com/z1984/archive/2013/03/07/2948104.html