递归sql server查询4 [英] Recursive sql server query 4
本文介绍了递归sql server查询4的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！
问题描述
我有两个表t1和t2与列objectid，parentid和objectname。对于每个t1.objectid id我需要导航t2直到t2.parentid为null并连接t2.objectname。
导航标准是t1.objectid = t2.parentid ..遍历直到t2.parent id为null。
$ b p>
创建表t1（objectid varchar（100），parentid varchar（100），objectname varchar（100））
go
创建表t2 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
插入到t2
选择'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 -675CE4AB8FEE'，'265-CC340F32-E97D-45CE-8019-26CE0FF99663'，'F6'
union
select'265-CC340F32-E97D-45CE-8019-26CE0FF99663'，NULL，'F7'
所需输出：
$ b b
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）
声明outer_cursor游标为
选择
objectid，
parentid，
objectname
从t1
open outer_cursor
fetch outer_cursor into @objectid，@ parentid，@ foldername
while（@@ FETCH_STATUS = 0）
BEGIN
/ *
插入到@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
- 选择@ 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 *从@tbl
解决方案
看起来您的示例数据无效因为在t2中，对象名称F4和F7引用相同的objectid。
除此之外，te下面的递归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为null
ORDER BY grp - 一旦样本数据在逻辑上正确就可以省略
I have 2 tables t1 and t2 with columns objectid, parentid and objectname. for each t1.objectid id I need to navigate t2 until t2.parentid is null and concatenate t2.objectname. The navigation criteria is that t1.objectid=t2.parentid .. traverse until t2.parent id is null. I tried cursor however I am not getting desired result.
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'
Desired output:
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
Solution (not working)
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
解决方案
It looks like your sample data are invalid since in t2 the objectnames F4 and F7 refer to the same objectid.
Other than that, te following recursive cte should return the expected result:
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
这篇关于递归sql server查询4的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
查看全文
