表 A
id fid tid
1 12 10
2 10 8
3 8 6
4 7 1
5 6 0
如果输入fid=12则输出:
id fid tid
1 12 10
2 10 8
3 8 6
5 6 0
就是根据fid和tid之间的关联选择结果,12继承自10,10又继承自8,8又继承自6 当发现的6的tid为0时候就不需要再寻找 所以有上面结果
请教sql的写法.
分不够会追加刚才发了一个帖子结果问反了,汗一个..已经把刚才的结掉了重新开.
id fid tid
1 12 10
2 10 8
3 8 6
4 7 1
5 6 0
如果输入fid=12则输出:
id fid tid
1 12 10
2 10 8
3 8 6
5 6 0
就是根据fid和tid之间的关联选择结果,12继承自10,10又继承自8,8又继承自6 当发现的6的tid为0时候就不需要再寻找 所以有上面结果
请教sql的写法.
分不够会追加刚才发了一个帖子结果问反了,汗一个..已经把刚才的结掉了重新开.
DECLARE @t TABLE(ID char(3),PID char(3),Name nvarchar(10))
INSERT @t SELECT '001',NULL ,'山东省'
UNION ALL SELECT '002','001','烟台市'
UNION ALL SELECT '004','002','招远市'
UNION ALL SELECT '003','001','青岛市'
UNION ALL SELECT '005',NULL ,'四会市'
UNION ALL SELECT '006','005','清远市'
UNION ALL SELECT '007','006','小分市'--深度排序显示处理
--生成每个节点的编码累计(相同当单编号法的编码)
DECLARE @t_Level TABLE(ID char(3),Level int,Sort varchar(8000))
DECLARE @Level int
SET @Level=0
INSERT @t_Level SELECT ID,@Level,ID
FROM @t
WHERE PID IS NULL
WHILE @@ROWCOUNT>0
BEGIN
SET @Level=@Level+1
INSERT @t_Level SELECT a.ID,@Level,b.Sort+a.ID
FROM @t a,@t_Level b
WHERE a.PID=b.ID
AND b.Level=@Level-1
END--显示结果
SELECT a.*
FROM @t a,@t_Level b
WHERE a.ID=b.ID
ORDER BY b.Sort
/*--结果
ID PID Name
------ --------- ----------
001 NULL 山东省
002 001 烟台市
004 002 招远市
003 001 青岛市
005 NULL 四会市
006 005 清远市
007 006 小分市
--*/
create table BOM(ID INT,PID INT,MSG VARCHAR(1000))
insert into BOM select 1,0,NULL
insert into BOM select 2,1,NULL
insert into BOM select 3,1,NULL
insert into BOM select 4,2,NULL
insert into BOM select 5,3,NULL
insert into BOM select 6,5,NULL
insert into BOM select 7,6,NULL
go--创建用户定义函数用于取每个父节点下子节点的采购配置信息
create function f_getChild(@ID VARCHAR(10))
returns @t table(ID VARCHAR(10),PID VARCHAR(10),Level INT)
as
begin
declare @i int
set @i = 1
insert into @t select ID,PID,@i from BOM where PID = @ID
while @@rowcount<>0
begin
set @i = @i + 1
insert into @t
select
a.ID,a.PID,@i
from
BOM a,@t b
where
a.PID=b.ID and b.Level = @i-1
end
return
end
go--执行查询
select ID from dbo.f_getChild(3)
go--输出结果
/*
ID
----
5
6
7
*/--删除测试数据
drop function f_getChild
drop table BOM
(
id int,
fid int,
tid int
)insert tb values(1,12,10)
insert tb values(2,10,8)
insert tb values(3,8,6)
insert tb values(4,7,1)
insert tb values(5,6,0)declare @t table(id int,fid int,tid int,level int)
declare @level int
declare @fid int
select @level=1,@fid=12insert @t select id,fid,tid,@level from tb where fid=@fid
while @@rowcount>0
begin
set @level=@level+1
insert @t select a.id,a.fid,a.tid,@level
from tb a,@t b
where a.fid=b.tid and b.level=@level-1
endselect id,fid,tid from @t order by leveldrop table tb/*
id fid tid
----------- ----------- -----------
1 12 10
2 10 8
3 8 6
5 6 0(所影响的行数为 4 行)
*/
create table ta(id int,fid int,tid int)
insert ta select 1 , 12 , 10
insert ta select 2 , 10 , 8
insert ta select 3 , 8 , 6
insert ta select 4 , 7 , 1
insert ta select 5 , 6 , 0
go
create function f_getChild(@ID int)
returns @t table(ID VARCHAR(10),PID VARCHAR(10),Level INT)
as
begin
declare @i int
set @i = 1
insert into @t select fID,tID,@i from ta where fID = @ID
while @@rowcount<>0
begin
set @i = @i + 1
insert into @t
select
a.fID,a.tID,@i
from
ta a,@t b
where
a.fID=b.PID and b.Level = @i-1
end
return
end
go
select * from ta where fid in(
select ID from dbo.f_getChild(12))
/*id fid tid
----------- ----------- -----------
1 12 10
2 10 8
3 8 6
5 6 0
*/
drop table tadrop function f_getChild
insert a select 1, 12, 10
insert a select 2, 10, 8
insert a select 3, 8, 6
insert a select 4, 7, 1
insert a select 5, 6, 0 go
create function GetRecordset(@fid int)
returns @t table(id int,fid int,tid int)
begin
insert @t select * from a where fid=@fid
while not exists(select 1 from @t where tid=0)
begin
insert @t select * from a b where exists(select 1 from @t where tid=b.fid)
end
return
end go
select distinct * from dbo.getRecordset(12)
--result
/*id fid tid
----------- ----------- -----------
1 12 10
2 10 8
3 8 6
5 6 0(所影响的行数为 4 行)*/
as
begin
while exists(select 1 from tb where fid = @fid)
begin
insert into tmp select * from tb where fid = @fid
select @fid = tid from tb where fid = @fid
end
end
gocreate table tb(id INT, fid int, tid int)
insert into tb values(1, 12, 10 )
insert into tb values(2, 10, 8 )
insert into tb values(3, 8 , 6 )
insert into tb values(4, 7 , 1 )
insert into tb values(5, 6 , 0 ) create table tmp(id INT, fid int, tid int)
goexec my_proc 12
select * from tmpdrop table tb,tmp
drop procedure my_proc/*
id fid tid
----------- ----------- -----------
1 12 10
2 10 8
3 8 6
5 6 0(4 行受影响)
*/