SQL 递归查询示例.docx
《SQL 递归查询示例.docx》由会员分享，可在线阅读，更多相关《SQL 递归查询示例.docx（15页珍藏版）》请在冰点文库上搜索。
SQL递归查询示例
SQL递归查询示例：
createtable#EnterPrise
（
Departmentnvarchar（50）,--部门名称
ParentDeptnvarchar（50）,--上级部门
DepartManagenvarchar（30）--部门经理
）
insertinto#EnterPriseselect'技术部','总经办','Tom'
insertinto#EnterPriseselect'商务部','总经办','Jeffry'
insertinto#EnterPriseselect'商务一部','商务部','ViVi'
insertinto#EnterPriseselect'商务二部','商务部','Peter'
insertinto#EnterPriseselect'程序组','技术部','GiGi'
insertinto#EnterPriseselect'设计组','技术部','yoyo'
insertinto#EnterPriseselect'专项组','程序组','Yue'
insertinto#EnterPriseselect'总经办','','Boss'
--查询部门经理是Tom的下面的部门名称
;withhgoas
（
select*,0asrankfrom#EnterPrisewhereDepartManage='Tom'
unionall
selecth.*,h1.rank+1from#EnterPrisehjoinhgoh1onh.ParentDept=h1.Department
）
select*fromhgo
/*
Department ParentDept DepartManage rank
---------------------------------------------------------------------
技术部 总经办 Tom 0
程序组 技术部 GiGi 1
设计组 技术部 yoyo 1
专项组 程序组 Yue 2
*/
--查询部门经理是GiGi的上级部门名称
;withhgoas
（
select*,0asrankfrom#EnterPrisewhereDepartManage='GiGi'
unionall
selecth.*,h1.rank+1from#EnterPrisehjoinhgoh1onh.Department=h1.ParentDept
）
select*fromhgo
/*
Department ParentDept DepartManage rank
------------------------------------------ ----------- -----------
程序组 技术部 GiGi 0
技术部 总经办 Tom 1
总经办 Boss 2
*/
--===========================================================================================================
createtable#tt
（idint,
parentidint,
namevarchar（20））
insert#tt
select1,0,'上海市'unionall
select2,1,'虹口区'unionall
select3,1,'徐汇区'unionall
select4,1,'浦东新区'unionall
select5,2,'江湾镇'unionall
select6,2,'虹口足球场'unionall
select7,5,'吉买盛'unionall
select8,5,'易卜莲花'unionall
select9,5,'农工商'unionall
select10,6,'吉买盛'
withcteas（
select*,0aslevlfrom#ttwhereid=2
unionall
selecta.*,b.levl+1from#tta,ctebwherea.parentid=b.id
）
select*fromctewherelevl>0
-- id parentidname levl
-----------------------------------------------------
5 2江湾镇 1
6 2虹口足球场 1
10 6吉买盛 2
7 5吉买盛 2
8 5易卜莲花 2
9 5农工商 2
==============================================================================================
ifnotobject_id（'Tab'）isnull
droptableTab
Go
CreatetableTab（[Id]int,[ParentId]int,[Name]nvarchar（50））
InsertTab
select1,0,N'上海市'unionall
select2,1,N'虹口区'unionall
select3,1,N'徐汇区'unionall
select4,1,N'浦东新区'unionall
select5,2,N'江湾镇'unionall
select6,2,N'虹口足球场'unionall
select7,5,N'吉买盛'unionall
select8,5,N'易卜莲花'unionall
select9,5,N'农工商'unionall
select10,6,N'吉买盛'
Go
IFOBJECT_ID（'P_GetParentId'）isnotnulldropprocP_GetParentId
go
CreateprocP_GetParentId
（
@ParentIdint
）
as
begin
declare@Deltable（IDint,[ParentId]int）
insert@DelselectID,[ParentId]fromTaba whereexists（select1fromTabwherea.Id=2）
while @@rowcount>0
begin
insert@Del
selecta.ID,a.[ParentId]
fromTaba
innerjoin@Delbonb.ID=a.[ParentId]
wherenotexists（select1from@DelwhereID=a.ID）
end
select*from@Del
end
gO
--TEST
EXECP_GetParentId2
--结果
--
/*
ID ParentId
----------------------
2 1
5 2
6 2
7 5
8 5
9 5
10 6
*/
--============================================================================================
--SQLcode
/*
标题：
SQLSERVER2000中查询指定节点及其所有子节点的函数（表格形式显示）
作者：
爱新觉罗·毓华（十八年风雨,守得冰山雪莲花开）
时间：
2008-05-12
地点：
广东深圳
*/
createtabletb（idvarchar（3）,pidvarchar（3）,namevarchar（10））
insertintotbvalues（'001',null ,'广东省'）
insertintotbvalues（'002','001','广州市'）
insertintotbvalues（'003','001','深圳市'）
insertintotbvalues（'004','002','天河区'）
insertintotbvalues（'005','003','罗湖区'）
insertintotbvalues（'006','003','福田区'）
insertintotbvalues（'007','003','宝安区'）
insertintotbvalues（'008','007','西乡镇'）
insertintotbvalues（'009','007','龙华镇'）
insertintotbvalues（'010','007','松岗镇'）
go
--查询指定节点及其所有子节点的函数
createfunctionf_cid（@IDvarchar（3））returns@t_leveltable（idvarchar（3）,levelint）
as
begin
declare@levelint
set@level=1
insertinto@t_levelselect@id,@level
while@@ROWCOUNT>0
begin
set@level=@level+1
insertinto@t_levelselecta.id,@level
fromtba,@t_Levelb
wherea.pid=b.idandb.level=@level-1
end
return
end
go
--调用函数查询001（广东省）及其所有子节点
selecta.*fromtba,f_cid（'001'）bwherea.id=b.idorderbya.id
/*
id pid name
------------------
001 NULL广东省
002 001 广州市
003 001 深圳市
004 002 天河区
005 003 罗湖区
006 003 福田区
007 003 宝安区
008 007 西乡镇
009 007 龙华镇
010 007 松岗镇
（所影响的行数为10行）
*/
--调用函数查询002（广州市）及其所有子节点
selecta.*fromtba,f_cid（'002'）bwherea.id=b.idorderbya.id
/*
id pid name
------------------
002 001 广州市
004 002 天河区
（所影响的行数为2行）
*/
--调用函数查询003（深圳市）及其所有子节点
selecta.*fromtba,f_cid（'003'）bwherea.id=b.idorderbya.id
/*
id pid name
------------------
003 001 深圳市
005 003 罗湖区
006 003 福田区
007 003 宝安区
008 007 西乡镇
009 007 龙华镇
010 007 松岗镇
（所影响的行数为7行）
*/
droptabletb
dropfunctionf_cid
/*
SQLcode
@@ROWCOUNT：
返回受上一语句影响的行数。
返回类型：
integer。
注释：
任何不返回行的语句将这一变量设置为0，如IF语句。
示例：
下面的示例执行UPDATE语句并用@@ROWCOUNT来检测是否有发生更改的行。
*/
UPDATEauthorsSETau_lname='Jones'WHEREau_id='999-888-7777'
IF@@ROWCOUNT=0
print'Warning:
Norowswereupdated'
/*
结果：
（所影响的行数为0行）
Warning:
Norowswereupdated
SQLcode
*/
/*
标题：
SQLSERVER2005中查询指定节点及其所有子节点的函数（表格形式显示）
作者：
爱新觉罗·毓华（十八年风雨,守得冰山雪莲花开）
时间：
2010-02-02
地点：
新疆乌鲁木齐
*/
createtabletb（idvarchar（3）,pidvarchar（3）,namenvarchar（10））
insertintotbvalues（'001',null ,N'广东省'）
insertintotbvalues（'002','001',N'广州市'）
insertintotbvalues（'003','001',N'深圳市'）
insertintotbvalues（'004','002',N'天河区'）
insertintotbvalues（'005','003',N'罗湖区'）
insertintotbvalues（'006','003',N'福田区'）
insertintotbvalues（'007','003',N'宝安区'）
insertintotbvalues（'008','007',N'西乡镇'）
insertintotbvalues（'009','007',N'龙华镇'）
insertintotbvalues（'010','007',N'松岗镇'）
go
DECLARE@IDVARCHAR（3）
--查询ID='001'的所有子节点
SET@ID='001'
;WITHTAS
（
SELECTID,PID,NAME
FROMTB
WHEREID=@ID
UNIONALL
SELECTA.ID,A.PID,A.NAME
FROMTBASAJOINTASBONA.PID=B.ID
）
SELECT*FROMTORDERBYID
/*
ID PID NAME
------------------
001 NULL广东省
002 001 广州市
003 001 深圳市
004 002 天河区
005 003 罗湖区
006 003 福田区
007 003 宝安区
008 007 西乡镇
009 007 龙华镇
010 007 松岗镇
（10行受影响）
*/
--查询ID='002'的所有子节点
SET@ID='002'
;WITHTAS
（
SELECTID,PID,NAME
FROMTB
WHEREID=@ID
UNIONALL
SELECTA.ID,A.PID,A.NAME
FROMTBASAJOINTASBONA.PID=B.ID
）
SELECT*FROMTORDERBYID
/*
ID PID NAME
------------------
002 001 广州市
004 002 天河区
（2行受影响）
*/
--查询ID='003'的所有子节点
SET@ID='003'
;WITHTAS
（
SELECTID,PID,NAME
FROMTB
WHEREID=@ID
UNIONALL
SELECTA.ID,A.PID,A.NAME
FROMTBASAJOINTASBONA.PID=B.ID
）
SELECT*FROMTORDERBYID
/*
ID PID NAME
------------------
003 001 深圳市
005 003 罗湖区
006 003 福田区
007 003 宝安区
008 007 西乡镇
009 007 龙华镇
010 007 松岗镇
（7行受影响）
*/
droptabletb
--注：
除ID值不一样外，三个SQL语句是一样的。
--===============================================================================
declare@ttable（idintidentity（1,1）,parentidint,namevarchar（10））
insert@t（parentid,name）select0,'上海市'
unionallselect1,'虹口区'
unionallselect1,'徐汇区'
unionallselect1,'浦东新区'
unionallselect2,'江湾镇'
unionallselect2,'虹口足球场'
unionallselect5,'吉买盛'
unionallselect5,'易卜莲花'
unionallselect5,'农工商'
unionallselect6,'吉买盛'
select*from@t
;withtbas
（
select*from@t
whereid=2
unionall
selecta.*from@tajointbbona.parentid=b.id
）
select*fromtb
orderbyid
/*
（10行受影响）
id parentid name
--------------------------------
1 0 上海市
2 1 虹口区
3 1 徐汇区
4 1 浦东新区
5 2 江湾镇
6 2 虹口足球场
7 5 吉买盛
8 5 易卜莲花
9 5 农工商
10 6 吉买盛
（10行受影响）
id parentid name
--------------------------------
2 1 虹口区
5 2 江湾镇
6 2 虹口足球场
7 5 吉买盛
8 5 易卜莲花
9 5 农工商
10 6 吉买盛
（7行受影响）
*/
--============================================================================
drop table tb
go
create table tb（code int,su_code int）
insert into tb（code,su_code） values（1,null）
insert into tb（code,su_code） values（11,1）
insert into tb（code,su_code） values（12,1）
insert into tb（code,su_code） values（111,11）
insert into tb（code,su_code） values（112,11）
insert into tb（code,su_code） values（121,12）
insert in
升级会员 