在开发中经常会遇到无限级分类的问题,最常见的是一些无限级目录之类的,如果你对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]
GO
CREATE CLUSTERED INDEX [IX_Tree] ON [dbo].[Tree]([PID]) ON [PRIMARY]
GO
ALTER TABLE [dbo].[Tree] WITH NOCHECK ADD
CONSTRAINT [PK_Tree] PRIMARY KEY NONCLUSTERED
(
[ID]
) ON [PRIMARY] ,
CONSTRAINT [子ID不能等于父ID] CHECK ([ID] <> [PID])
GO
ALTER TABLE [dbo].[Tree] ADD
CONSTRAINT [FK_Tree_Tree] FOREIGN KEY
(
[PID]
) REFERENCES [dbo].[Tree] (
[ID]
)
GO
/**//****** 对象: 用户定义的函数 dbo.fGetTreeTable ******/
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
GO
--实际数据
Insert Tree(PID, Name) values(null, 公司)
Insert Tree(PID, Name) values(3, IT)
Insert Tree(PID, Name) values(1, Fin)
Insert Tree(PID, Name) values(5, XZ)
Insert Tree(PID, Name) values(1, HR)
GO
直接查询Select * from dbo.fGetTreeTable(null)就可以输入所有记录。