在开发过程中,我们经常遇到请求数据时,以及父节点查找所有子节点以及依据子节点查找所有父节点问题,其中最常用的就是依据某个省、查找该省下面的市、区、街道以及依据某个街道,查找到该街道所在区、市、省。
在此记录一下常用 SQL 语句;数据库设计如下:
一、由于在 SQL SERVER2000 中不支持递归,所以我们需要使用函数 (表值函数) 进行查询
1、依据父节点查找所有子节点
函数定义如下:
- create
- function f_GetAllChildrenAreaById(@id int)--得到一个地区的所有下级returns@temp_tb_areas table(id int, vName varchar(50), iParentId int, iLevel int) asbegin insert@temp_tb_areas select id,
- vName,
- iParentId,
- iLevel from tb_Areas where id = @id
- while@@rowcount > 0 begin insert@temp_tb_areas select id,
- vName,
- iParentId,
- iLevel from tb_Areas where iParentId in (select id from@temp_tb_areas) and id not in (select id from@temp_tb_areas) end returnend
调用方法如下:
- select * from f_GetAllChildrenAreaById(1)
显示结果如下:
2、依据子节点查找所有父节点
函数定义如下:
- create
- function[dbo]. [f_GetAllParentAreaById](@id int) returns@temp_tb_areas table(Id int, vName varchar(50), iParentId int, iLevel int) as begin insert into@temp_tb_areas select id,
- vName,
- iParentId,
- iLevel from tb_Areas where ID = @id
- while@@rowcount < >0 begin insert into@temp_tb_areas select a.Id,
- a.vName,
- a.iParentId,
- a.iLevel from tb_Areas as a inner join@temp_tb_areas as b on a.Id = b.iParentId and not exists(select 1 from@temp_tb_areas where iParentId = a.iParentId) end
- return end
调用方法如下:
- select * from [f_GetAllParentAreaById](13)
显示结果:
二、在 SQL SERVER 2005 以上,直接使用递归就可以了,不需要在定义函数了,
1、依据父节点、查找所有子节点,代码如下:
- create PROCEDURE [dbo].[GetAllChildrenAreaById] @id intASBEGINwith cte as(select ID,vName,iParentId,iLevel from tb_Areas where ID = @idunion allselect a.ID,a.vName,a.iParentId,a.iLevel from tb_Areas a join cte b on a.iParentId = b.Id)select * from cte order by ID ascend
2、依据子节点查找所有父节点
- create PROCEDURE[dbo]. [GetAllParentById]@id intASBEGINwith cte(id, iParentId, vName, iLevel) as(select id, iParentId, vName, iLevel, iStatus from tb_Areas where id = @idunion allselect b.id, b.iParentId, b.vName, b.iLevel, b.iStatus from cte A, tb_Areas B where a.iParentId = b.id) select id,
- iParentId,
- vName,
- iLevel from cte order by iLevel ascEND
对于 SQL SERVER 2005 以及以上的 SQL SERVER 版本,调用的话,可以直接使用存储过程,如果使用 SQL 语句的话,可以直接将 BEGIN/END 中的预计进行查询。
三、父节点查找子节点,以一定格式显示全名称,代码如下:
- WITH cte AS(select ID, vName, iParentId, CAST(vName AS VARCHAR(100)) AS fullnamefrom[tb_Areas] WHERE iParentID = 0 and ID = 1UNION ALL SELECT a.id, a.vNAME, a.iParentID, CAST(b.fullname + '/' + a.vName AS VARCHAR(100)) AS FULLnameFROM[tb_Areas] a INNER JOIN cte b ON a.iParentID = b.id) SELECT id,
- vname,
- fullname FROM cte
就爱阅读 www.92to.com 网友整理上传, 为您提供最全的知识大全, 期待您的分享,转载请注明出处。
来源: http://www.92to.com/bangong/2017/04-05/19989546.html