-
- if object_id('[tb]') is not null drop table [tb]
- go
- create table [tb]([id] int,[yid] varchar(2),[t1] varchar(3),[t2] varchar(3))
- insert [tb]
- select 1,null,'s','2' union all
- select 2,'1','22','22s' union all
- select 3,null,'ss','12' union all
- select 4,'2','ss2','2' union all
- select 5,'3','1','w' union all
- select 6,'4','2','q1'
- --select * from [tb]
- create function fn_haha(@id int)
- returns @tb table([id] int,[yid] varchar(2),[t1] varchar(3),[t2] varchar(3))
- as
- begin
- insert @tb select * from tb where id=@id
- while @@rowcount>0
- insert @tb
- select a.*
- from (select * from tb where id not in(select id from @tb)) a
- join @tb b on a.id=b.yid or a.yid=b.id
- return
- end
- go
- select * from dbo.fn_haha(2) order by id
- /*
- id yid t1 t2
- ----------- ---- ---- ----
- 1 NULL s 2
- 2 1 22 22s
- 4 2 ss2 2
- 6 4 2 q1
- (4 行受影响)
- */
- select * from dbo.fn_haha(3) order by id
- /*
- id yid t1 t2
- ----------- ---- ---- ----
- 3 NULL ss 12
- 5 3 1 w
- (2 行受影响)
- */
- create table tb(id varchar(3) , pid varchar(3) , name1 varchar(10) , name2 varchar(10))
- insert into tb values('1', null , 's' , '2')
- insert into tb values('2', '1' , '22' , '22')
- insert into tb values('3', null , 'ss' , '12')
- insert into tb values('4', '2' , 'ss2', '2')
- insert into tb values('5', '3' , '1' , 'w')
- insert into tb values('6', '4' , '2' , 'q1')
- go
- --查询指定节点及其所有子节点的函数
- create function f_cid(@ID varchar(3)) returns @t_level table(id varchar(3) , level int)
- as
- begin
- declare @level int
- set @level = 1
- insert into @t_level select @id , @level
- while @@ROWCOUNT > 0
- begin
- set @level = @level + 1
- insert into @t_level select a.id , @level
- from tb a , @t_Level b
- where a.pid = b.id and b.level = @level - 1
- end
- return
- end
- go
- --查询指定节点及其所有父节点的函数
- create function f_pid(@id varchar(3)) returns @t_level table(id varchar(3))
- as
- begin
- insert into @t_level select @id
- select @id = pid from tb where id = @id and pid is not null
- while @@ROWCOUNT > 0
- begin
- insert into @t_level select @id select @id = pid from tb where id = @id and pid is not null
- end
- return
- end
- go
- --调用函数查询'1'
- select a.* from tb a , f_cid('1') b where a.id = b.id
- union
- select a.* from tb a , f_pid('1') b where a.id = b.id
- order by a.id
- /*
- id pid name1 name2
- ---- ---- ---------- ----------
- 1 NULL s 2
- 2 1 22 22
- 4 2 ss2 2
- 6 4 2 q1
- (所影响的行数为 4 行)
- */
- --调用函数查询'2'
- select a.* from tb a , f_cid('2') b where a.id = b.id
- union
- select a.* from tb a , f_pid('2') b where a.id = b.id
- order by a.id
- /*
- id pid name1 name2
- ---- ---- ---------- ----------
- 1 NULL s 2
- 2 1 22 22
- 4 2 ss2 2
- 6 4 2 q1
- (所影响的行数为 4 行)
- */
- --调用函数查询'3'
- select a.* from tb a , f_cid('3') b where a.id = b.id
- union
- select a.* from tb a , f_pid('3') b where a.id = b.id
- order by a.id
- /*
- id pid name1 name2
- ---- ---- ---------- ----------
- 3 NULL ss 12
- 5 3 1 w
- (所影响的行数为 2 行)
- */
- drop table tb
- drop function f_pid , f_cid
问题描述:
table1:
id Yid T1 T2 ...
1 s 2
2 1 22 22s
3 ss 12
4 2 ss2 2
5 3 1 w
6 4 2 q1
问题是 请查找出 所有 id=2[或1或4或6] 有关系的数据 [Yid和id相同的也找出来]
得到的答案是:
id Yid T1 T2 ...
1 s 2
2 1 22 22s
4 2 ss2 2
6 4 2 q1
如果 id=3[或5] 都得到
id Yid T1 T2 ...
3 ss 12
5 3 1 w
-----------------
sql 解决方法: