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

写sql题，这个sql是一个自关联表，有ID,ParentID,Status。其中Status=1表示有继承关系，请写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

转载于:https://www.cnblogs.com/luckjun/archive/2011/05/27/2059417.html