Sql关联表，取出继承自己的所有数据和被自己继承的所有数据

CREATE TABLE [dbo].[test] (
        [id] [int] IDENTITY (1, 1) NOT NULL ,
        [ParentID] [int] NOT NULL ,
        [Status] [int] NOT NULL 
) ON [PRIMARY]
GO

CREATE  proc gg @id int
as
    declare @t table(id int,parentid int, status int)
-- 开始检测父节点
insert @t select * from test where id = @id and status =1 
    while @@rowcount > 0 
        insert @t select a.* from Test as a inner join @t as b
        on a.id = b.ParentID and b.status =1 and a.id not in(select id from @t)
delete from @t where id = @id
--删除自身
--开始检测子节点
    insert @t select * from test where id = @id
    while @@rowcount > 0 
        insert @t select a.* from Test as a inner join @t as b
        on a.ParentID = b.id and a.status =1 and a.id not in(select id from @t)
 delete from @t where id = @id
    select * from @t


GO