如何递归找出2个节点之间的所有记录？
表结构如下，要递归找出指定的2个id之间的所有记录。这个2id在一个父子链上
id name parentid
---------------------
不用function和procedure，直接用sql可以得到么？如果用function or procedure ,怎么写效率最高？
[解决办法]
给个实例测试环境
create table #temp
(chd varchar(50),
name varchar(50),
fath varchar(50)
)
insert into #temp
select '1 ', '华北 ', '0 ' union all select '2 ', '东北 ', '0 ' union all select '3 ', '华东 ', '0 ' union all select '4 ', '华南 ', '0 ' union all select '5 ', '西南 ', '0 ' union all select '6 ', '西北 ', '0 ' union all select '9 ', '国外 ', '0 ' union all select '111 ', '北京 ', '1 ' union all select '112 ', '天津 ', '1 ' union all select '113 ', '河北 ', '1 ' union all select '114 ', '陕西 ', '1 ' union all select '115 ', '山西 ', '1 ' union all select '116 ', '内蒙 ', '1 ' union all select '221 ', '黑龙江 ', '2 ' union all select '222 ', '吉林 ', '2 ' union all select '223 ', '辽宁 ', '2 ' union all select '331 ', '上海 ', '3 ' union all select '332 ', '江苏 ', '3 ' union all select '333 ', '浙江 ', '3 ' union all select '334 ', '安徽 ', '3 ' union all select '335 ', '福建 ', '3 ' union all select '336 ', '江西 ', '3 ' union all select '337 ', '山东 ', '3 ' union all select '441 ', '河南 ', '4 ' union all select '442 ', '湖北 ', '4 ' union all select '443 ', '湖南 ', '4 ' union all select '444 ', '海南 ', '4 ' union all select '445 ', '广西 ', '4 ' union all select '446 ', '广东 ', '4 ' union all select '551 ', '重庆 ', '5 ' union all select '552 ', '四川 ', '5 ' union all select '553 ', '云南 ', '5 ' union all select '554 ', '贵州 ', '5 ' union all select '661 ', '甘肃 ', '6 ' union all select '662 ', '西藏 ', '6 ' union all select '663 ', '宁夏 ', '6 ' union all select '664 ', '青海 ', '6 ' union all select '665 ', '新疆 ', '6 ' union all select '991 ', '美国 ', '9 ' union all select '992 ', '日本 ', '9 ' union all select '11301 ', '石家庄 ', '113 ' union all select '11401 ', '西安 ', '114 ' union all select '11501 ', '太原 ', '115 ' union all select '11601 ', '呼市 ', '116 ' union all select '22101 ', '哈尔滨 ', '221 ' union all select '22201 ', '长春 ', '222 ' union all select '22301 ', '沈阳 ', '223 ' union all select '33201 ', '南京 ', '332 ' union all select '33202 ', '苏州 ', '332 ' union all select '33301 ', '杭州 ', '333 ' union all select '33302 ', '宁波 ', '333 ' union all select '33401 ', '合肥 ', '334 ' union all select '33501 ', '福州 ', '335 ' union all select '33502 ', '厦门 ', '335 ' union all select '33601 ', '南昌 ', '336 ' union all select '33701 ', '济南 ', '337 ' union all select '33702 ', '青岛 ', '337 ' union all select '44101 ', '郑州 ', '441 ' union all select '44201 ', '武汉 ', '442 ' union all select '44301 ', '长沙 ', '443 ' union all select '44401 ', '海口 ', '444 ' union all select '44501 ', '南宁 ', '445 ' union all select '44502 ', '桂林 ', '445 ' union all select '44601 ', '广州 ', '446 ' union all select '44602 ', '深圳 ', '446 ' union all select '55201 ', '成都 ', '552 ' union all select '55301 ', '昆明 ', '553 ' union all select '55401 ', '贵阳 ', '554 ' union all select '66101 ', '兰州 ', '661 ' union all select '66201 ', '拉萨 ', '662 ' union all select '66301 ', '银川 ', '663 ' union all select '66401 ', '西宁 ', '664 ' union all select '66501 ', '乌鲁木齐 ', '665 '
select * from #temp
查询语句
declare @begin varchar(10)
declare @end varchar(10)
declare @sql varchar(8000)
set @sql= ' '
select @begin= '66101 '--起始的子id
select @end= '0 '--结尾的父id
while @end <> @begin
begin
set @sql=@sql+ 'select * from #aa where chd= '+@begin+ ' union all '
select @begin=fath from #aa where chd=@begin
end
select @sql=left(@sql,len(@sql)-10)
print @sql
exec(@sql)
结果：
------
chd name fath
66101 兰州 661
661 甘肃 6
6 西北 0
[解决办法]
可以参考：
--查上部,最大32
--@DepartmentID 开始层
--@SuperiorID 上级
--DataDepartment 部门表
--Superior 上级ID
CREATE FUNCTION GetSuperiorDerpt(@DepartmentID int,@SuperiorID int)
RETURNS @TableName TABLE (DepartmentID int)
AS
BEGIN
DECLARE @TempID int
SELECT @TempID=Superior FROM DataDepartment WHERE DepartmentID=@DepartmentID
INSERT INTO @TableName SELECT @TempID WHERE @TempID IS NOT NULL
IF (NOT @TempID IS NULL) AND @TempID <> @SuperiorID
INSERT INTO @TableName SELECT * FROM dbo.GetSuperiorDerpt(@TempID,@SuperiorID)
RETURN
END