create table t (parent varchar(10), child varchar(10),qty numeric(9,2) )insert into t select 'FG001', 'SFG001', 1 union all select 'FG001' , 'SFG002', 1 union all select 'FG001' ,'SFG003', 1 union all select 'SFG001', 'WIP001', 2 union all select 'SFG001' ,'WIP002', 2 union all select 'SFG002' ,'WIP003', 3 union all select 'SFG002' ,'WIP004', 3 union all select 'SFG002' ,'WIP005', 2 union all select 'SFG003' ,'WIP006', 3 union all select 'WIP001' ,'RAW001', 2.66 union all select 'WIP001' ,'RAW002' , 2.33 union all select 'WIP002' ,'RAW003' , 3.21 union all select 'WIP003' ,'RAW004' , 1.89 union all select 'WIP003' ,'RAW005' , 1.86 union all select 'RAW001','KKK001', 3.25 union all select 'RAW004','KKK003', 4.26 union all select 'KKK001','WWW005', 5.23 二:创建函数(a:树型结构显示) create function test(@parent VARCHAR(10)) returns @t table(parent Nvarchar(10),child Nvarchar(10),qty numeric(9,2), level int,sort Nvarchar(1000)collate Latin1_General_BIN ) as begin declare @level int set @level=1 insert into @t select parent,child,qty,@level,parent+child from t where parent=@parent collate Latin1_General_BIN while @@rowcount>0 begin set @level=@level+1 insert @t select a.parent,a.child,a.qty*b.qty,@level,b.sort+a.child from t a ,@t b where a.parent=b.child collate Latin1_General_BIN and b.level=@level-1 end return end--调用函数 select level, space(level*2)+'|--' + child as 'product', qty from dbo.test('FG001') order by sort --结果 /**//* level product qty 1 |--SFG001 1.00 2 |--WIP001 2.00 3 |--RAW001 5.32 4 |--KKK001 17.29 5 |--WWW005 90.43 3 |--RAW002 4.66 2 |--WIP002 2.00 3 |--RAW003 6.42 1 |--SFG002 1.00 2 |--WIP003 3.00 3 |--RAW004 5.67 4 |--KKK003 24.15 3 |--RAW005 5.58 2 |--WIP004 3.00 2 |--WIP005 2.00 1 |--SFG003 1.00 2 |--WIP006 3.00(17 row(s) affected) */
--类似这样? create table 表1(pid int identity(1,1),parent int,desn varchar(50)) insert into 表1 select 0,'体育用品' insert into 表1 select 0,'家用电器' insert into 表1 select 1,'足球' insert into 表1 select 1,'篮球' insert into 表1 select 3,'阿迪达斯足球' insert into 表1 select 2,'电视机' insert into 表1 select 2,'冰箱' insert into 表1 select 6,'海尔电视机' insert into 表1 select 7,'海尔冰箱' create table 表2(itemid int identity(1,1),pid int,name varchar(50)) insert into 表2 select 5,'1型号足球' insert into 表2 select 9,'双开门冰箱' insert into 表2 select 4,'牛皮篮球' insert into 表2 select 9,'车用冰箱' insert into 表2 select 6,'液晶电视' create function f_cid(@pid int) returns varchar(500) as begin declare @t table(pid int,parent int,desn varchar(50),lev int) declare @lev int set @lev=1 insert into @t select *,@lev from 表1 where pid=@pid while(@@rowcount>0) begin set @lev=@lev+1 insert into @t select a.*,@lev from 表1 a,@t b where a.parent=b.pid and b.lev=@lev-1 end declare @cids varchar(500) select @cids=isnull(@cids+',','')+ltrim(pid) from @t order by lev return @cids end select *, 商品count=(select count(1) from 表2 where charindex(','+ltrim(pid)+',',','+dbo.f_cid(a.pid)+',')>0) from 表1 a
参考如下的树处理.create table tb(id int, name varchar(10), pid int, px int) insert into tb values(0 , '栏目分类', 0 , 1) insert into tb values(1 , '动物' , 0 , 1) insert into tb values(2 , '视频' , 0 , 2) insert into tb values(3 , '老虎' , 1 , 1) insert into tb values(4 , '狮子' , 1 , 2) insert into tb values(5 , '搞笑' , 2 , 1) go--查询指定节点及其所有子节点的函数 CREATE FUNCTION f_Cid(@ID int) RETURNS @t_Level TABLE(ID int,Level int) AS BEGIN DECLARE @Level int SET @Level=1 INSERT @t_Level SELECT @ID,@Level WHILE @@ROWCOUNT>0 BEGIN SET @Level=@Level+1 INSERT @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 --调用函数查询id = 1及其所有子节点 SELECT a.* FROM tb a,f_Cid(1) b WHERE a.ID=b.ID /* id name pid px ----------- ---------- ----------- ----------- 1 动物 0 1 3 老虎 1 1 4 狮子 1 2 (所影响的行数为 3 行) */drop table tb drop function dbo.f_cid
select count(distinct b.id) from 表a a,表B b where b.typeid = a.typeid and a.parentid is null
declare @sid int while exists(select 1 from 表a where parentid is not null) select @sid = parentid from 表a where typeid = @sid print @sid
(parent varchar(10),
child varchar(10),qty numeric(9,2)
)insert into t
select 'FG001', 'SFG001', 1 union all
select 'FG001' , 'SFG002', 1 union all
select 'FG001' ,'SFG003', 1 union all
select 'SFG001', 'WIP001', 2 union all
select 'SFG001' ,'WIP002', 2 union all
select 'SFG002' ,'WIP003', 3 union all
select 'SFG002' ,'WIP004', 3 union all
select 'SFG002' ,'WIP005', 2 union all
select 'SFG003' ,'WIP006', 3 union all
select 'WIP001' ,'RAW001', 2.66 union all
select 'WIP001' ,'RAW002' , 2.33 union all
select 'WIP002' ,'RAW003' , 3.21 union all
select 'WIP003' ,'RAW004' , 1.89 union all
select 'WIP003' ,'RAW005' , 1.86 union all
select 'RAW001','KKK001', 3.25 union all
select 'RAW004','KKK003', 4.26 union all
select 'KKK001','WWW005', 5.23
二:创建函数(a:树型结构显示)
create function test(@parent VARCHAR(10))
returns @t table(parent Nvarchar(10),child Nvarchar(10),qty numeric(9,2),
level int,sort Nvarchar(1000)collate Latin1_General_BIN )
as
begin
declare @level int
set @level=1
insert into @t
select parent,child,qty,@level,parent+child
from t
where parent=@parent collate Latin1_General_BIN
while @@rowcount>0
begin
set @level=@level+1
insert @t
select a.parent,a.child,a.qty*b.qty,@level,b.sort+a.child
from t a ,@t b
where a.parent=b.child collate Latin1_General_BIN
and b.level=@level-1
end
return
end--调用函数
select
level,
space(level*2)+'|--' + child as 'product',
qty
from
dbo.test('FG001')
order by
sort
--结果
/**//*
level product qty
1 |--SFG001 1.00
2 |--WIP001 2.00
3 |--RAW001 5.32
4 |--KKK001 17.29
5 |--WWW005 90.43
3 |--RAW002 4.66
2 |--WIP002 2.00
3 |--RAW003 6.42
1 |--SFG002 1.00
2 |--WIP003 3.00
3 |--RAW004 5.67
4 |--KKK003 24.15
3 |--RAW005 5.58
2 |--WIP004 3.00
2 |--WIP005 2.00
1 |--SFG003 1.00
2 |--WIP006 3.00(17 row(s) affected)
*/
--类似这样?
create table 表1(pid int identity(1,1),parent int,desn varchar(50))
insert into 表1 select 0,'体育用品'
insert into 表1 select 0,'家用电器'
insert into 表1 select 1,'足球'
insert into 表1 select 1,'篮球'
insert into 表1 select 3,'阿迪达斯足球'
insert into 表1 select 2,'电视机'
insert into 表1 select 2,'冰箱'
insert into 表1 select 6,'海尔电视机'
insert into 表1 select 7,'海尔冰箱'
create table 表2(itemid int identity(1,1),pid int,name varchar(50))
insert into 表2 select 5,'1型号足球'
insert into 表2 select 9,'双开门冰箱'
insert into 表2 select 4,'牛皮篮球'
insert into 表2 select 9,'车用冰箱'
insert into 表2 select 6,'液晶电视'
create function f_cid(@pid int)
returns varchar(500)
as
begin
declare @t table(pid int,parent int,desn varchar(50),lev int)
declare @lev int
set @lev=1
insert into @t select *,@lev from 表1 where pid=@pid
while(@@rowcount>0)
begin
set @lev=@lev+1
insert into @t select a.*,@lev from 表1 a,@t b
where a.parent=b.pid and b.lev=@lev-1
end
declare @cids varchar(500)
select @cids=isnull(@cids+',','')+ltrim(pid) from @t order by lev
return @cids
end
select *,
商品count=(select count(1) from 表2 where charindex(','+ltrim(pid)+',',','+dbo.f_cid(a.pid)+',')>0)
from 表1 a
insert into tb values(0 , '栏目分类', 0 , 1)
insert into tb values(1 , '动物' , 0 , 1)
insert into tb values(2 , '视频' , 0 , 2)
insert into tb values(3 , '老虎' , 1 , 1)
insert into tb values(4 , '狮子' , 1 , 2)
insert into tb values(5 , '搞笑' , 2 , 1)
go--查询指定节点及其所有子节点的函数
CREATE FUNCTION f_Cid(@ID int) RETURNS @t_Level TABLE(ID int,Level int)
AS
BEGIN
DECLARE @Level int
SET @Level=1
INSERT @t_Level SELECT @ID,@Level
WHILE @@ROWCOUNT>0
BEGIN
SET @Level=@Level+1
INSERT @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 --调用函数查询id = 1及其所有子节点
SELECT a.* FROM tb a,f_Cid(1) b WHERE a.ID=b.ID
/*
id name pid px
----------- ---------- ----------- -----------
1 动物 0 1
3 老虎 1 1
4 狮子 1 2
(所影响的行数为 3 行)
*/drop table tb
drop function dbo.f_cid
while exists(select 1 from 表a where parentid is not null)
select @sid = parentid from 表a where typeid = @sid
print @sid