--尝试sql2005的新特性，递归
--取得所有子信息
------------------------------------
--用途： 创建功能表
--项目名称：
--创建人： 王召冠
--说明：
--时间：
------------------------------------
CREATE TABLE tFunction
(
OID bigint primary key identity(1, 1),
vName nvarchar(50) not null,
vLabel nvarchar(50),
lParent bigint,
iDispOrder int,
vImageUrl nvarchar(50),
vRemark nvarchar(500),
iVersion int
)
GO
-- 停止自动增长键
SET IDENTITY_INSERT [tFunction] ON
GO
INSERT INTO tFunction(OID, vName, vLabel, iDispOrder, vImageUrl, lParent, vRemark) select 1, '系统设置', '系统设置', 8, '', null, ''
INSERT INTO tFunction(OID, vName, vLabel, iDispOrder, vImageUrl, lParent, vRemark) select 2, '用户管理', '用户管理', 6, '', null, ''
INSERT INTO tFunction(OID, vName, vLabel, iDispOrder, vImageUrl, lParent, vRemark) select 3, '功能设置', '功能设置', 6, '', 1, ''
INSERT INTO tFunction(OID, vName, vLabel, iDispOrder, vImageUrl, lParent, vRemark) select 4, '用户管理', '用户管理', 2, '', 2, ''
GO
-- 启动自动增长
SET IDENTITY_INSERT [tFunction] OFF
GO
-- 递归查询
WITH abc
AS
(
SELECT *, 0 AS iLevel
FROM [tFunction] AS t
WHERE t.[OID] = 1
UNION ALL
SELECT t.*, [abc].iLevel + 1 AS iLevel
FROM [tFunction] AS t
JOIN [abc] ON t.[lParent] = [abc].OID
)
SELECT *
FROM [abc]
