我没说清楚,就是找出a,b之间所经过的所有节点,这些树状节点,在数据库里是用id,parentid,维护之间关系的,现在给出2个id,找出它们这个branch的所经过的节点
解决方案 »
- MS-SQL中如何把表中不允许为空的字段改为允许为空?
- 求包含关键字最多的sql语句
- 大数据量的数据查询及显示
- 初学SQL server,有什么好的书籍推荐?
- 如何将B表和C表的数据赋值给A表的对应字段
- vivianfdlpw() 快来呀 !! 你的设置 一个用户 不能执行 存贮过程!!!!
- 如何在SQL里求得某两行的同一列内容的相关性?
- 请问怎样读取数据库中某个表某字段的描述(也就是字段的说明文字)?
- 如何把SQL2000的记录写入文本文件(用ASP)实现
- Win8企业版不能安装SQL Server 2005企业版,显示组件为灰色
- 这样的子查询有没有办法简化?
- 100w的数据量,sp执行了6个小时。
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
declare @begin varchar(10)
declare @end varchar(10)
declare @sql varchar(8000)
set @sql=''
select @begin='66101'--起始的子id
select @end='6'--结尾的子id
while (select fath from #temp where chd=@end)<>@begin
begin
set @sql=@sql+'select * from #temp where chd='+@begin+' union all '
select @begin=fath from #temp where chd=@begin
end
select @sql=left(@sql,len(@sql)-10)
print @sql
exec(@sql)
http://topic.csdn.net/t/20050723/17/4163676.html
--查上級部門,最大層數為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