树形菜单的经典算法(用Function实现估计没有比这个更简洁的算法啦)

--生成测试数据
CREATE TABLE T_TABLE(
id int IDENTITY(1,1) NOT NULL,
upid int NULL,
title [varchar](50))

INSERT INTO T_TABLE (upid,title) values(0,'1TITLE')
INSERT INTO T_TABLE (upid,title) values(0,'2TITLE')
INSERT INTO T_TABLE (upid,title) values(0,'3TITLE')
INSERT INTO T_TABLE (upid,title) values(2,'4TITLE')
INSERT INTO T_TABLE (upid,title) values(2,'5TITLE')
INSERT INTO T_TABLE (upid,title) values(4,'6TITLE')
INSERT INTO T_TABLE (upid,title) values(3,'7TITLE')
INSERT INTO T_TABLE (upid,title) values(3,'8TITLE')
INSERT INTO T_TABLE (upid,title) values(7,'9TITLE')
INSERT INTO T_TABLE (upid,title) values(8,'10TITLE')
INSERT INTO T_TABLE (upid,title) values(9,'11TITLE')
INSERT INTO T_TABLE (upid,title) values(7,'12TITLE')
INSERT INTO T_TABLE (upid,title) values(12,'13TITLE')

--树形菜单的函数
CREATE FUNCTION [dbo].[getTree] (@id int)
RETURNS @t table(id int,upid int,title varchar(50),TLevel int,LevelSort varchar(1000) PRIMARY KEY (LevelSort)) AS
begin
declare @TLevel int
set @TLevel=0
insert into @t select id,upid,title,@TLevel,str(ID,10) from T_TABLE where id=@id
while @@rowCount>0
begin
set @TLevel=@TLevel+1
insert into @t select a.id,a.upid,a.title,@TLevel,b.LevelSort+str(a.id,10) from T_TABLE a join @t b on b.id=a.upid where b.TLevel=@TLevel-1
end
return
End
LevelSort的生成算法比较重要
Str(ID,10) LeverSort一定要是固定长度的列.Str用空格填充多余的空间