select * from tb t1 join tb t2 on t1.BM_ID=T2.BM_ID and T1.YH_ID=t2.SJ_ID
--两级 select * from tb a join tb b on a.BM_ID=b.BM_ID and a.YH_ID=b.SJ_ID
---多级 参考 -->Title:Generating test data -->Author:wufeng4552 -->Date :2009-09-30 08:52:38 set nocount on if object_id('tb','U')is not null drop table tb go create table tb(ID int, ParentID int) insert into tb select 1,0 insert into tb select 2,1 insert into tb select 3,1 insert into tb select 4,2 insert into tb select 5,3 insert into tb select 6,5 insert into tb select 7,6 -->Title:查找指定節點下的子結點 if object_id('Uf_GetChildID')is not null drop function Uf_GetChildID go create function Uf_GetChildID(@ParentID int) returns @t table(ID int) as begin insert @t select ID from tb where ParentID=@ParentID while @@rowcount<>0 begin insert @t select a.ID from tb a inner join @t b on a.ParentID=b.id and not exists(select 1 from @t where id=a.id) end return end go select * from dbo.Uf_GetChildID(5) /* ID ----------- 6 7 */ -->Title:查找指定節點的所有父結點 if object_id('Uf_GetParentID')is not null drop function Uf_GetParentID go create function Uf_GetParentID(@ID int) returns @t table(ParentID int) as begin insert @t select ParentID from tb where ID=@ID while @@rowcount!=0 begin insert @t select a.ParentID from tb a inner join @t b on a.id=b.ParentID and not exists(select 1 from @t where ParentID=a.ParentID) end return end go select * from dbo.Uf_GetParentID(2) /* ParentID ----------- 1 0 */本文来自CSDN博客,转载请标明出处:http://blog.csdn.net/wufeng4552/archive/2009/09/30/4619995.aspx
select * from table where YH_ID = 'U003' and BM_ID in (select BM_ID from table where a.YH_ID in (select SJ_ID from table where YH_ID = 'U003') )
from tb t1
join tb t2 on t1.BM_ID=T2.BM_ID and T1.YH_ID=t2.SJ_ID
select
*
from
tb a
join tb b on
a.BM_ID=b.BM_ID and a.YH_ID=b.SJ_ID
-->Title:Generating test data
-->Author:wufeng4552
-->Date :2009-09-30 08:52:38
set nocount on
if object_id('tb','U')is not null drop table tb
go
create table tb(ID int, ParentID int)
insert into tb select 1,0
insert into tb select 2,1
insert into tb select 3,1
insert into tb select 4,2
insert into tb select 5,3
insert into tb select 6,5
insert into tb select 7,6
-->Title:查找指定節點下的子結點
if object_id('Uf_GetChildID')is not null drop function Uf_GetChildID
go
create function Uf_GetChildID(@ParentID int)
returns @t table(ID int)
as
begin
insert @t select ID from tb where ParentID=@ParentID
while @@rowcount<>0
begin
insert @t select a.ID from tb a inner join @t b
on a.ParentID=b.id and
not exists(select 1 from @t where id=a.id)
end
return
end
go
select * from dbo.Uf_GetChildID(5)
/*
ID
-----------
6
7
*/
-->Title:查找指定節點的所有父結點
if object_id('Uf_GetParentID')is not null drop function Uf_GetParentID
go
create function Uf_GetParentID(@ID int)
returns @t table(ParentID int)
as
begin
insert @t select ParentID from tb where ID=@ID
while @@rowcount!=0
begin
insert @t select a.ParentID from tb a inner join @t b
on a.id=b.ParentID and
not exists(select 1 from @t where ParentID=a.ParentID)
end
return
end
go
select * from dbo.Uf_GetParentID(2)
/*
ParentID
-----------
1
0
*/本文来自CSDN博客,转载请标明出处:http://blog.csdn.net/wufeng4552/archive/2009/09/30/4619995.aspx
where YH_ID = 'U003'
and BM_ID in (select BM_ID from table
where a.YH_ID in (select SJ_ID from table where YH_ID = 'U003')
)