高分挑战，在单编号的树型结构树中，怎样查找到一个节点的*有条件*父节点

Question

表结构如下
declare @temptable table(fresourceviewstructureguid uniqueidentifier ,ffoldername varchar(50),ftreeid varchar(100),fallowmaintenance bit)
insert into @temptable select '4f62221d-e40f-4353-867f-a2faaa13c662','根','000',1
union all select 'BBB2542B-40CC-4E91-9565-6DD3C9ED159F','aaaa','000001',0 
union all select '29638B94-1A04-4CE2-AC81-9B9D0C3C331B','bbbb','000001001',0 
union all select 'D0A8A76F-ED97-4849-9D7C-F7B318C8C451','cccc','000001002',NULL
union all select 'D643AAC4-F06C-4831-A023-DE2EA418B103','dddd','000001002001',NULL
union all select 'A260BCD3-3189-4F13-A0B9-3A45E9079DCB','eeee','000002',0
union all select 'BE695BDA-8566-4FFC-8EE5-5C2014F0CFCC','ffff','000003',0
union all select '46758991-F990-4B00-B00B-4D138E337256','gggg','000004',NULLselect * from @temptable其中ftreeid字段表示的是节点的编号，根节点的fallowmaintenance字段一定有值，还有三条记录的fallowmaintenance字段值为NULL,此字段的值这正是我要计算的，计算的方法就是依据ftreeid找到它的有条件父节点，有条件的意思是fallowmaintenance必须有值不为NULL，以'D643AAC4-F06C-4831-A023-DE2EA418B103'这条记录为例，它的第一层父节点是'D0A8A76F-ED97-4849-9D7C-F7B318C8C451'但这条记录的fallowmaintenance的值为NULL，所以需要继续找上一层父节点，可以找到'BBB2542B-40CC-4E91-9565-6DD3C9ED159F'这条记录是合适的，得到它的fallowmaintenance字段值为0，所以我要计算的子节点字段的值与它二层父节点值相同为0。由于在得出张临时表之前我已将记录按ftreeid排序，如果以游标从上至下遍历临时表去找父节点，将不需要判断父节点的fallowmaintenance字段值是否为NULL,但我更希望大家能给出通用函数的写法。最后这张表的记录为
4F62221D-E40F-4353-867F-A2FAAA13C662 根 000          1
BBB2542B-40CC-4E91-9565-6DD3C9ED159F aaaa 000001          0
29638B94-1A04-4CE2-AC81-9B9D0C3C331B bbbb 000001001          0
D0A8A76F-ED97-4849-9D7C-F7B318C8C451 cccc 000001002          0
D643AAC4-F06C-4831-A023-DE2EA418B103 dddd 000001002001 0
A260BCD3-3189-4F13-A0B9-3A45E9079DCB eeee 000002          0
BE695BDA-8566-4FFC-8EE5-5C2014F0CFCC ffff 000003          0
46758991-F990-4B00-B00B-4D138E337256 gggg 000004          1

Answer 1

select A.*,ISNULL(A.fallowmaintenance,B.fallowmaintenance) AS fallowmaintenance1 from @temptable A LEFT JOIN @temptable B
ON A.fallowmaintenance IS NULL AND A.ftreeid<>B.ftreeid AND A.ftreeid LIKE B.ftreeid +'%' AND B.fallowmaintenance IS NOT NULL
AND B.ftreeid=(SELECT MAX(ftreeid) FROM @temptable WHERE A.ftreeid LIKE ftreeid +'%' AND fallowmaintenance IS NOT NULL)

Answer 2

用函数，要求你的原始表不能用表变量必须用永久表，假设表名为Treetable 
create function fn_fallowmaintenance (
@ftreeid varchar(100)
) 
returns bit
as
begin
    declare @r bit
    select @r=fallowmaintenance from Treetable where ftreeid=@ftreeid
    while @r is null
    begin
       set @ftreeid=left(@ftreeid,len(@ftreeid)-3)
       select @r=fallowmaintenance from Treetable where ftreeid=@ftreeid
    end 
    return @r
endgo--调用更新
update Treetable
set fallowmaintenance=dbo.fn_fallowmaintenance(ftreeid)
where fallowmaintenance is null--没有测试

Answer 3

是不是按ftreeid来找上级,比如'000001002001'->'000001002'->'000001',条件是当fallowmaintenance为空就一直向上找,最后取出'000001'对应的fallowmaintenance的值(也就是0)

Answer 4

select A.*,ISNULL(A.fallowmaintenance,B.fallowmaintenance) AS fallowmaintenance1 from @temptable A LEFT JOIN @temptable B
ON A.fallowmaintenance IS NULL AND A.ftreeid<>B.ftreeid AND A.ftreeid LIKE B.ftreeid +'%' AND B.fallowmaintenance IS NOT NULL
AND B.ftreeid=(SELECT MAX(ftreeid) FROM @temptable WHERE A.ftreeid LIKE ftreeid +'%' AND fallowmaintenance IS NOT NULL)同小山羊的做法！

Answer 5

试了下小山羊和冒牌经理的方法，都能通过，
其中小山羊的条件是不是过多了点，我去掉
AND A.ftreeid LIKE B.ftreeid +'%' AND B.fallowmaintenance IS NOT NULL
这一串查询结果也是正确的，不知这段是不是必须的，
冒牌经理的函数很简练，用起来sql语句要少写很多，但我的记录是来自两张表的连接，fallowmaintenance字段是在另张表里的，所以函数里没办法写表名，不知用函数还有没有别的办法