欢迎您访问我爱IT技术网，今天小编为你分享的编程技术是：【SQL Server 2005数据库中表的递归查询】，下面是详细的分享！
SQL Server 2005数据库中表的递归查询
递归查询对于同一个表父子关系的计算提供了很大的方便，这个示例使用了SQL server 2005中的递归查询,使用的表是CarParts，这个表存储了一辆汽车的所有零件以及结构，part为零件单位，subpart为子零件，Qty为数量。
具体示例如下：
*/
CREATE TABLE CarParts
(
CarID INT NOT NULL,
Part VARCHAR(15),
SubPart VARCHAR(15),
Qty INT
)
GO
INSERT CarParts VALUES (1, 'Body', 'Door', 4)
INSERT CarParts VALUES (1, 'Body', 'Trunk Lid', 1)
INSERT CarParts VALUES (1, 'Body', 'Car Hood', 1)
INSERT CarParts VALUES (1, 'Door', 'Handle', 1)
INSERT CarParts VALUES (1, 'Door', 'Lock', 1)
INSERT CarParts VALUES (1, 'Door', 'Window', 1)
INSERT CarParts VALUES (1, 'Body', 'Rivets', 1000)
INSERT CarParts VALUES (1, 'Door', 'Rivets', 100)
INSERT CarParts VALUES (1, 'Door', 'Mirror', 1)
INSERT CarParts VALUES (1, 'Mirror', 'small_Mirror', 4)
GO
SELECT * FROM CarParts
GO
WITH CarPartsCTE(SubPart, Qty)
AS
(
-- 固定成员 (AM):
-- SELECT查询无需参考CarPartsCTE
-- 递归从此处开始
SELECT SubPart, Qty
FROM CarParts
WHERE Part='Body'
UNION ALL
-- 递归成员 (RM):
-- SELECT查询参考CarPartsCTE
-- 使用现有数据往下一层展开
SELECT CarParts.SubPart, CarPartsCTE.Qty * CarParts.Qty
FROM CarPartsCTE
INNER JOIN CarParts ON CarPartsCTE.SubPart=CarParts.Part
WHERE CarParts.CarID=1
)
SELECT SubPart,Qty AS TotalNUM
FROM CarPartsCTE
drop table CarParts
--------------------------------result---------------------------------------
CarID Part SubPart Qty
----------- --------------- --------------- -----------
1 Body Door 4
1 Body Trunk Lid 1
1 Body Car Hood 1
1 Door Handle 1
1 Door Lock 1
1 Door Window 1
1 Body Rivets 1000
1 Door Rivets 100
1 Door Mirror 1
1 Mirror small_Mirror 4
(10 row(s) affected)
SubPart TotalNUM
--------------- -----------
Door 4
Trunk Lid 1
Car Hood 1
Rivets 1000
Handle 4
Lock 4
Window 4
Rivets 400
Mirror 4
small_Mirror 16
(10 row(s) affected)
示例：
以下示例显示经理以及向经理报告的雇员的层次列表。
WITH DirectReports(groupid, member, EmployeeLevel,type) AS
(
SELECT groupid, member, 0,type AS EmployeeLevel
FROM groupinfo
WHERE groupid='finance_company'
UNION ALL
SELECT e.groupid, e.member, EmployeeLevel + 1,e.type
FROM groupinfo e
INNER JOIN DirectReports d
ON e.groupid=d.member
)
SELECT b.nickname,groupid, member, EmployeeLevel,type
FROM DirectReports,userbasicinfo b
where DirectReports.member=b.id
and type=1
以上所分享的是关于SQL Server 2005数据库中表的递归查询，下面是编辑为你推荐的有价值的用户互动：
相关问题：ms sql 2005 递归查询如何实现
答：with t1 as (select cast(1 as varchar(20)) path,* from Co_ItemNameSet where itemid=5union allselect cast(t1.path+'.'+cast(row_number() over (order by t2.itemid) as varchar(10)) as varchar(20)), t2.* from Co_ItemNameSet t2 join t... >>详细
相关问题：SQLSERVER 2005 查询数据库所有表格
答：select name from sysobjects where xtype='u' 查询数据库中的表名~ >>详细
相关问题：sql server 2005 中查询一个数据库中所有表的数据...
答：declare @sql nvarchar(100) declare @tableName nvarchar(100) declare cur cursor for select name from sysobjects where xtype='U' --搜索所有表名 open cur while @@fetch_status=0 begin set @sql='select * from [' + @tableName + '] ' ... >>详细
- 评论列表（网友评论仅供网友表达个人看法，并不表明本站同意其观点或证实其描述）
-