数据结构请看一下图示:
参考:
1. CFAreaStatKey和ParentKey行程tree结构。
2. 如果某个节点和其节点以下的子结点的IsDisable都为1的话,那么就将记录移除掉。
在图中,CFAreaStatKey为29和31的满足条件#2,应该从结果集中移除掉。请教各位赐予SQL,谢谢。
参考:
1. CFAreaStatKey和ParentKey行程tree结构。
2. 如果某个节点和其节点以下的子结点的IsDisable都为1的话,那么就将记录移除掉。
在图中,CFAreaStatKey为29和31的满足条件#2,应该从结果集中移除掉。请教各位赐予SQL,谢谢。
insert into tb
select 1,null,1 union
select 2,1,0 union
select 3,1,1 union
select 4,null,0 union
select 5,1,1 union
select 6,5,1 union
select 7,4,1
;with cte as
(
select id from tb where tp=1 and pid is null
union all
select b.id from cte a join tb b on a.id=b.pid
where tp=1
) delete tb where id in(select id from cte)select * from tb/*
id pid tp
----------- ----------- -----------
2 1 0
4 NULL 0
7 4 1(3 行受影响)
UNION
SELECT 29, 28, 1
UNION
SELECT 31, 28, 1
UNION
SELECT 30, 29, 0
UNION
SELECT 32, 30, 0
UNION
SELECT 33, NULL, 0SELECT * FROM #t--想要的结果集中不要包含key为29, 31的数据,因为29,31作为一个独立的tree节点,可以从tree结构中移除掉ROLLBACK
UNION
SELECT 29, 28, 1
UNION
SELECT 30, 28, 1
UNION
SELECT 31, 29, 0
UNION
SELECT 32, 30, 0
UNION
SELECT 33, NULL, 0SELECT * FROM #t--想要的结果集中不要包含key为29, 31的数据,因为29,31作为一个独立的tree节点,可以从tree结构中移除掉ROLLBACK
where IsDisabled=1 and
not exists(select 1 from #t where ParentUKey=a.UKey)
union
select 29, 28, 1
union
select 30, 28, 1
union
select 31, 29, 1
union
select 32, 30, 0
union
select 33, null, 0这样子的吧!
|------29(disable = 1)
|--------31(disable = 1)
|------30
|--------32
33
29和31都是disabled = 1,可以直接将这个分支从树里面移除掉。
where IsDisabled=1 and
exists(select 1 from #t where ParentUKey=a.UKey and IsDisabled=1)
union
select 29, 28, 1
union
select 31, 29, 1
union
select 30, 28, 0
union
select 32, 30, 0
union
select 33, null, 031是29的子结点,并且31和29数据行的disable属性都为1,所以29连同31一起都应该从结果集中过滤掉,不知道说明白没有~
where IsDisabled=1 and
(exists(select 1 from #t where ParentUKey=a.UKey and IsDisabled=1)
or
exists(select 1 from #t where UKey=a.ParentUKey and IsDisabled=1))
create table tb(ukey int, parentukey int, isdisabled bit)
insert into tb
select 28, null, 0
union
select 29, 28, 1
union
select 31, 29, 1
union
select 30, 28, 0
union
select 32, 30, 0
union
select 33, null, 0
gocreate function get_tb(@ukey int)
returns @e table
(
ukey int,
cnt int,
iscnt int
)
as
begin
declare @lev int
declare @re table(ukey int,parentukey int,isdisabled bit,lev int)
set @lev = 1
insert into @re select *,@lev from tb where ukey = @ukey
while(@@rowcount > 0)
begin
set @lev = @lev + 1
insert into @re
select a.*,@lev
from tb a join @re b on a.parentukey = b.ukey and @lev = b.lev+1
end
insert into @e
select ukey,(select count(*) from @re),(select count(*) from @re where isdisabled=1)
from @re
return
end
goselect *,(case when exists (select 1 from dbo.get_tb(t.ukey) where cnt = iscnt) then 1 else 0 end)flag
into #tb
from tb tdelete t
from tb t join #tb e on t.ukey = e.ukey
where e.flag = 1select *
from tbdrop function get_tb
drop table tb,#tb/*************ukey parentukey isdisabled
----------- ----------- ----------
28 NULL 0
30 28 0
32 30 0
33 NULL 0(4 行受影响)
写函数貌似效率不高啊!
create table tb(ukey int, parentukey int, isdisabled bit)
insert into tb
select 28, null, 0
union
select 29, 28, 1
union
select 31, 29, 1
union
select 30, 28, 0
union
select 32, 30, 0
union
select 33, null, 0
gocreate function get_tb(@ukey int)
returns @e table
(
ukey int,
cnt int,
iscnt int
)
as
begin
declare @lev int
declare @re table(ukey int,parentukey int,isdisabled bit,lev int)
set @lev = 1
insert into @re select *,@lev from tb where ukey = @ukey
while(@@rowcount > 0)
begin
set @lev = @lev + 1
insert into @re
select a.*,@lev
from tb a join @re b on a.parentukey = b.ukey and @lev = b.lev+1
end
insert into @e
select ukey,(select count(*) from @re),(select count(*) from @re where isdisabled=1)
from @re
return
end
godelete t
from tb t
where exists (select 1 from dbo.get_tb(t.ukey) where cnt = iscnt)select *
from tbdrop function get_tb
drop table tb/****************ukey parentukey isdisabled
----------- ----------- ----------
28 NULL 0
30 28 0
32 30 0
33 NULL 0(4 行受影响)
另外AcHerat,请问可以不用function来完成吗?我需要放在一个sp里面。