日期:2014-05-18  浏览次数:20527 次

如何递归找出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 ',