在开发中经常会遇到无限级分类的问题,最常见的是一些无限级目录之类的,如果你对SQL Server还不是太熟悉或者还不太熟悉SQL语言的话,这将是一件很困难的事情,也许你最后能实现类似的功能,不过可能花费的时间很长,效率也不是很理想,下面的示例也许能给你一个启示。
/*
**创建表***
*/
CREATE TABLE [ dbo ] . [ Tree ] (
[ ID ] [ int ] IDENTITY ( 1 , 1 ) NOT NULL ,
[ PID ] [ int ] NULL ,
[ Name ] [ varchar ] ( 10 ) COLLATE Chinese_PRC_CI_AS NULL
) ON [ PRIMARY ]
/* ***创建自定义函数*** */
CREATE FUNCTION dbo.fGetTreeTable
(
@ID int = null
)
RETURNS @Tab TABLE (ID int , PID int , Name varchar ( 10 ), Lev int ) -- 返回结果的表结构
AS
BEGIN
Declare @lev int
Set @lev = 0
While @lev = 0 or @@ROWCount > 0
Begin
Set @Lev = @Lev + 1
Insert @Tab (ID, PID, Name, Lev)
Select ID, PID, Name, @Lev From Tree Where ( @Lev = 1 and ((PID = @ID ) or ( @ID is null and PID is null ))) or (PID in ( Select ID From @Tab Where Lev = @Lev - 1 ))
order by ID
End
RETURN
END
CREATE TABLE [ dbo ] . [ Tree ] (
[ ID ] [ int ] IDENTITY ( 1 , 1 ) NOT NULL ,
[ PID ] [ int ] NULL ,
[ Name ] [ varchar ] ( 10 ) COLLATE Chinese_PRC_CI_AS NULL
) ON [ PRIMARY ]
/* ***创建自定义函数*** */
CREATE FUNCTION dbo.fGetTreeTable
(
@ID int = null
)
RETURNS @Tab TABLE (ID int , PID int , Name varchar ( 10 ), Lev int ) -- 返回结果的表结构
AS
BEGIN
Declare @lev int
Set @lev = 0
While @lev = 0 or @@ROWCount > 0
Begin
Set @Lev = @Lev + 1
Insert @Tab (ID, PID, Name, Lev)
Select ID, PID, Name, @Lev From Tree Where ( @Lev = 1 and ((PID = @ID ) or ( @ID is null and PID is null ))) or (PID in ( Select ID From @Tab Where Lev = @Lev - 1 ))
order by ID
End
RETURN
END
直接查询Select * from dbo.fGetTreeTable(null)就可以输入所有记录。
说明:本文转载自:http://drc.cnblogs.com/articles/269062.html,如有问题可以向原作者提出。