[请教]如何对树结构里的节点进行查询？
在数据库表里有一个“目录”表，是一个树结构，保存有自己的编号，父节点编号，名称
文件表里保存有自己的编号，文件所在目录的编号，以及名称及数量
现在要查询要查询目录下的文件，包括其子目录的文件。因为目录下面可能一层套一层，因为SQL语句里没见过递归语句，所以我们现在的做法是先通过应用程序递归查询某个目录下所有的子目录，然后再组合一个查询文件的SQL语句。
假设查询的目录名称为 “第一层B”，编号为3
我们通过 select [编号] from [目录] where [父节点] = 3 语句不断递归返回的“编号”，拼接成一个子目录目录列表
然后通过 select * from [文件] where [目录编号] in (3, 6, 7, 9) 进行最终的文件查询。
不过这样的查询效率不用想大家都知道会很低了，现在想请教各位有没有什么高效的方法？
数据库类型为 Access,MySQL,MSSQL
CREATE TABLE [文件] (
[编号] [int] NULL,
[目录编号] [int] NOT NULL DEFAULT 0,
[名称] [varchar] NOT NULL,
[数量] [int] NOT NULL DEFAULT 0)
INSERT [文件] ([编号],[目录编号],[名称],[数量]) VALUES ( 1,1, '根节点文件1 ',1)
INSERT [文件] ([编号],[目录编号],[名称],[数量]) VALUES ( 2,2, 'A ',2)
INSERT [文件] ([编号],[目录编号],[名称],[数量]) VALUES ( 3,2, 'A ',1)
INSERT [文件] ([编号],[目录编号],[名称],[数量]) VALUES ( 4,3, 'B ',4)
INSERT [文件] ([编号],[目录编号],[名称],[数量]) VALUES ( 5,4, 'A1 ',1)
INSERT [文件] ([编号],[目录编号],[名称],[数量]) VALUES ( 6,5, 'C ',1)
INSERT [文件] ([编号],[目录编号],[名称],[数量]) VALUES ( 7,6, 'B1 ',5)
INSERT [文件] ([编号],[目录编号],[名称],[数量]) VALUES ( 8,7, 'B2 ',9)
INSERT [文件] ([编号],[目录编号],[名称],[数量]) VALUES ( 9,8, 'B11 ',11)
INSERT [文件] ([编号],[目录编号],[名称],[数量]) VALUES ( 10,8, 'B11 ',2)
CREATE TABLE [目录] (
[编号] [int] NULL,
[父节点] [int] NOT NULL DEFAULT 0,
[名称] [varchar] NOT NULL)
INSERT [目录] ([编号],[父节点],[名称]) VALUES ( 1,0, '根节点 ')
INSERT [目录] ([编号],[父节点],[名称]) VALUES ( 2,1, '第一层A ')
INSERT [目录] ([编号],[父节点],[名称]) VALUES ( 3,1, '第一层B ')
INSERT [目录] ([编号],[父节点],[名称]) VALUES ( 4,2, '第二层A1 ')
INSERT [目录] ([编号],[父节点],[名称]) VALUES ( 5,1, '第一层C ')
INSERT [目录] ([编号],[父节点],[名称]) VALUES ( 6,3, '第二层B1 ')
INSERT [目录] ([编号],[父节点],[名称]) VALUES ( 7,3, '第二层B2 ')
INSERT [目录] ([编号],[父节点],[名称]) VALUES ( 8,6, '第三层B11 ')
INSERT [目录] ([编号],[父节点],[名称]) VALUES ( 9,3, '第二层B3 ')
[解决办法]
--邹老大写的函数
--测试数据
CREATE TABLE tb(ID char(3),PID char(3),Name nvarchar(10))
INSERT tb SELECT '001 ',NULL , '山东省 '
UNION ALL SELECT '002 ', '001 ', '烟台市 '
UNION ALL SELECT '004 ', '002 ', '招远市 '
UNION ALL SELECT '003 ', '001 ', '青岛市 '
UNION ALL SELECT '005 ',NULL , '四会市 '
UNION ALL SELECT '006 ', '005 ', '清远市 '
UNION ALL SELECT '007 ', '006 ', '小分市 '
GO
--查询指定节点及其所有子节点的函数
CREATE FUNCTION f_Cid(@ID char(3))
RETURNS @t_Level TABLE(ID char(3),Level int)
AS
BEGIN
DECLARE @Level int
SET @Level=1
INSERT @t_Level SELECT @ID,@Level
WHILE @@ROWCOUNT> 0
BEGIN
SET @Level=@Level+1
INSERT @t_Level SELECT a.ID,@Level
FROM tb a,@t_Level b
WHERE a.PID=b.ID
AND b.Level=@Level-1
END
RETURN
END
GO
--调用函数查询002及其所有子节点
SELECT a.*
FROM tb a,f_Cid( '002 ') b
WHERE a.ID=b.ID
/*--结果
ID PID Name
------ ------- ----------
002 001 烟台市
004 002 招远市
--*/
[解决办法]
--建立測試環境
CREATE TABLE [文件] (
[编号] [int] NULL,
[目录编号] [int] NOT NULL DEFAULT 0,
[名称] [Nvarchar] (20) NOT NULL,
[数量] [int] NOT NULL DEFAULT 0)
INSERT [文件] ([编号],[目录编号],[名称],[数量]) VALUES ( 1,1, '根节点文件1 ',1)
INSERT [文件] ([编号],[目录编号],[名称],[数量]) VALUES ( 2,2, 'A ',2)
INSERT [文件] ([编号],[目录编号],[名称],[数量]) VALUES ( 3,2, 'A ',1)
INSERT [文件] ([编号],[目录编号],[名称],[数量]) VALUES ( 4,3, 'B ',4)
INSERT [文件] ([编号],[目录编号],[名称],[数量]) VALUES ( 5,4, 'A1 ',1)
INSERT [文件] ([编号],[目录编号],[名称],[数量]) VALUES ( 6,5, 'C ',1)
INSERT [文件] ([编号],[目录编号],[名称],[数量]) VALUES ( 7,6, 'B1 ',5)
INSERT [文件] ([编号],[目录编号],[名称],[数量]) VALUES ( 8,7, 'B2 ',9)
INSERT [文件] ([编号],[目录编号],[名称],[数量]) VALUES ( 9,8, 'B11 ',11)
INSERT [文件] ([编号],[目录编号],[名称],[数量]) VALUES ( 10,9, 'B12 ',2)
CREATE TABLE [目录] (
[编号] [int] NULL,
[父节点] [int] NOT NULL DEFAULT 0,
[名称] [Nvarchar](20) NOT NULL)
INSERT [目录] ([编号],[父节点],[名称]) VALUES ( 1,0, '根节点 ')
INSERT [目录] ([编号],[父节点],[名称]) VALUES ( 2,1, '第一层A ')
INSERT [目录] ([编号],[父节点],[名称]) VALUES ( 3,1, '第一层B ')
INSERT [目录] ([编号],[父节点],[名称]) VALUES ( 4,2, '第二层A1 ')
INSERT [目录] ([编号],[父节点],[名称]) VALUES ( 5,1, '第一层C ')
INSERT [目录] ([编号],[父节点],[名称]) VALUES ( 6,3, '第二层B1 ')
INSERT [目录] ([编号],[父节点],[名称]) VALUES ( 7,3, '第二层B2 ')
INSERT [目录] ([编号],[父节点],[名称]) VALUES ( 8,6, '第三层B11 ')
INSERT [目录] ([编号],[父节点],[名称]) VALUES ( 9,3, '第二层B3 ')
GO
--建立函數
Create Function F_GetChild(@ID Int)
Returns @Tree Table(编号 Int)
As
Begin
Insert @Tree Select 编号 From 目录 Where 编号 = @ID
While @@ROWCOUNT > 0
Insert @Tree Select A.编号 From 目录 A Inner Join @Tree B On A.父节点 = B.编号 And A.编号 Not In (Select 编号 From @Tree)
Return
End
GO
--測試
Select A.* From 文件 A
Inner Join dbo.F_GetChild(3) B
On A.目录编号 = B.编号
--刪除測試環境
Drop Table 目录, 文件
Drop Function F_GetChild
--結果
/*
编号目录编号名称数量
43B4
76B15
87B29
98B1111
109B122
*/
[解决办法]
similarly answer below:
HTTP://www.ebigear.com/Fund/PlayNews.php?NewsID=28340&ID=285885
