首页 | 新闻 | 交流 | 问吧 | 文档 | 手册 | 下载 | 博客

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

作者:  时间: 2011-05-27

写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