我有2个表T1与T2列OBJECTID,parentId的和对象名。 每个t1.objectid ID我需要导航T2直到t2.parentid为空并连接t2.objectname。 导航标准是t1.objectid = t2.parentid ..横动直到t2.parent id为null。 我试过光标不过我没有得到期望的结果。
SCHEMA
create table t1(objectid varchar(100), parentid varchar(100),objectname varchar(100))
go
create table t2(objectid varchar(100), parentid varchar(100),objectname varchar(100))
go
insert into t1
select '265-0151CDDF-F032-4E47-98B2-236127258C81',NULL,'F1'
union
select '265-091D3023-4B6A-4822-8409-AC0807DA9EB4','265-CC340F32-E97D-45CE-8019-26CE0FF99663','F2'
union
select '265-2684E597-5A0A-4656-B1D5-FED27F67ADC9','265-EEA1BB1D-E4F3-43C4-9D3B-625CE4AB8FEE','F3'
GO
insert into t2
select '265-0151CDDF-F032-4E47-98B2-236127258C81',NULL,'F1'
union
select '265-091D3023-4B6A-4822-8409-AC0807DA9EB4','265-CC340F32-E97D-45CE-8019-26CE0FF99663','F2'
union
select '265-CC340F32-E97D-45CE-8019-26CE0FF99663',NULL,'F4'
union
select '265-2684E597-5A0A-4656-B1D5-FED27F67ADC9','265-EEA1BB1D-E4F3-43C4-9D3B-625CE4AB8FEE','F5'
union
select '265-EEA1BB1D-E4F3-43C4-9D3B-625CE4AB8FEE','265-CC340F32-E97D-45CE-8019-26CE0FF99663','F6'
union
select '265-CC340F32-E97D-45CE-8019-26CE0FF99663',NULL,'F7'
所需的输出:
objectid parentid objectname
---------------------------------------- ----------- ----------
265-0151CDDF-F032-4E47-98B2-236127258C81 NULL F1
265-091D3023-4B6A-4822-8409-AC0807DA9EB4 NULL F4/F2
265-2684E597-5A0A-4656-B1D5-FED27F67ADC9 NULL F7/F6/F3
解决方案(不工作)
declare @objectid nvarchar(max)
declare @parentid nvarchar(max)
declare @foldername nvarchar(100)
Declare @tbl TABLE (objectid nvarchar(max), parentid nvarchar(max) ,fdn nvarchar(max))
declare @innerfoldername nvarchar(max)
declare @fdn nvarchar(max)
declare outer_cursor cursor for
Select
objectid,
parentid,
objectname
from t1
open outer_cursor
fetch outer_cursor into @objectid, @parentid,@foldername
while(@@FETCH_STATUS=0)
BEGIN
/*
insert into @tbl
SELECT @objectid, @parentid,@foldername
*/
declare inner_cursor cursor for
select parentid,objectname from t2 where objectid=@parentid
open inner_cursor
fetch inner_cursor into @parentid,@innerfoldername
set @fdn=@foldername
while(@@FETCH_STATUS=0)
BEGIN
set @fdn=@innerfoldername + '/' + @fdn
-- select @objectid,@parentid,@fdn
insert into @tbl
SELECT @objectid,@parentid,@fdn
fetch inner_cursor into @parentid,@innerfoldername
END
close inner_cursor
deallocate inner_cursor
fetch outer_cursor into @objectid, @parentid,@foldername
END
close outer_cursor
deallocate outer_cursor
select * from @tbl