递归SQL Server查询4(Recursive sql server query 4)

2019-10-20 17:43发布

我有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

Answer 1:

它看起来像你的样本数据是无效的,因为在T2的ObjectName F4和F7指同一OBJECTID。

除此之外,忒以下递归CTE应该返回预期的结果:

WITH MyCTE AS 
(
    SELECT  objectid as grp, parentid, CAST(objectname as varchar(201)) as objectname2
    FROM t1
    UNION ALL
    SELECT Mycte.grp, t2.parentid, CAST(t2.objectname as varchar(100)) +'/'+ CAST(mycte.objectname2 as varchar(100))
    FROM t2
    INNER JOIN MyCTE ON MyCTE.parentid = t2.objectid
)
SELECT grp, objectname2
FROM MyCTE
WHERE parentid is null
ORDER BY grp -- can be omitted once the sample data are logically correct


文章来源: Recursive sql server query 4