求一句SQL,100分问题!!!!!!!!!!!!!!!!!!!!!!!!!

[learnall]

有这样的树型结构表

CID PID CODE
1 0 01
2 1 02
3 0 03
4 2 04
.....

CID 为子记录 ID
PID 为父节点 ID
CODE 为编码

请问如何用最简短的并且效率最高的SQL语句 得出下列结果:

CID CODE
1 01
2 01-02
3 03
4 01-02-04
......

最有效的SQL 分数全给! 谢谢大家

[wgsasd311]

create function f_1(@cid int)
returns varchar(8000)
as
begin
declare @s varchar(8000)
set @s=''
select @s=@s+code,@cid=pid from tb where cid=@cid
while @@rowcount>0
begin
select @s=@s+code,@cid=pid from tb where cid=@cid
end
return @s
end
go
select cid,[code]=dbo.f_1(cid) from tb

[子陌红尘]

--生成测试数据
create table BOM(CID INT,PID INT,CODE VARCHAR(10))
insert into BOM select 1,0,'01'
insert into BOM select 2,1,'02'
insert into BOM select 3,0,'03'
insert into BOM select 4,2,'04'
go

--创建用户定义函数
create function f_getChild(@CID VARCHAR(10))
returns varchar(8000)
as
begin
declare @i int,@ret varchar(8000)
declare @t table(CID INT,PID INT,CODE VARCHAR(10),Level INT)
set @i = 1
insert into @t select CID,PID,CODE,@i from BOM where CID = @CID

while @@rowcount<>0
begin
set @i = @i + 1

insert into @t
select
a.CID,a.PID,a.CODE,@i
from
BOM a,@t b
where
a.CID=b.PID and b.Level = @i-1
end

select @ret = isnull(@ret,'')+'-'+CODE from @t order by level desc
set @ret = stuff(@ret,1,1,'')
return @ret
end
go

select CID,PID,CODE=dbo.f_getChild(CID) from BOM
go


drop function f_getChild
drop table BOM

[子陌红尘]

--生成测试数据
create table BOM(CID INT,PID INT,CODE VARCHAR(10))
insert into BOM select 1,0,'01'
insert into BOM select 2,1,'02'
insert into BOM select 3,0,'03'
insert into BOM select 4,2,'04'
go

--创建用户定义函数
create function f_getChild(@CID VARCHAR(10))
returns varchar(8000)
as
begin
declare @i int,@ret varchar(8000)
declare @t table(CID INT,PID INT,CODE VARCHAR(10),Level INT)
set @i = 1
insert into @t select CID,PID,CODE,@i from BOM where CID = @CID

while @@rowcount<>0
begin
set @i = @i + 1

insert into @t
select
a.CID,a.PID,a.CODE,@i
from
BOM a,@t b
where
a.CID=b.PID and b.Level = @i-1
end

select @ret = isnull(@ret,'')+'-'+CODE from @t order by level desc
set @ret = stuff(@ret,1,1,'')
return @ret
end
go


--执行查询
select CID,PID,CODE=dbo.f_getChild(CID) from BOM

--输出结果
/*
CID CODE
------ --------
1 01
2 01-02
3 03
4 01-02-04
*/

--删除测试数据
drop function f_getChild
drop table BOM

[vivianfdlpw]

create table A
(
CID int,
PID int,
CODE varchar(10)
)
insert A
select 1,0,'01' union
select 2,1,'02' union
select 3,0,'03' union
select 4,2,'04'
go

--创建函数
create function f_tb(@CID int)
returns @tb table(CID int,Code varchar(10))
as
begin
insert @tb select CID,Code from A where PID=@CID
while @@rowcount>0
begin
insert @tb
select A.CID
,B.Code+'-'+A.Code
from A
join @tb B on A.PID=B.CID
where A.CID not in(select CID from @tb)
end

return
end
go

--查询
select * from f_tb(0) order by CID

--删除测试环境
drop function f_tb
drop table A

--结果
/*
CID Code
----------- ----------
1 01
2 01-02
3 03
4 01-02-04

（所影响的行数为 4 行）
*/

[wgsasd311]

drop function dbo.f_1
create table tb(CID INT,PID INT,CODE VARCHAR(10))
insert into tb select 1,0,'01'
insert into tb select 2,1,'02'
insert into tb select 3,0,'03'
insert into tb select 4,2,'04'
go
create function f_1(@cid int)
returns varchar(8000)
as
begin
declare @s varchar(8000)
set @s=''
select @s=@s+'-'+code,@cid=pid from tb where cid=@cid
while @@rowcount>0
begin
select @s=@s+'-'+code,@cid=pid from tb where cid=@cid
end
return stuff(@s,1,1,'')
end
go
select cid,[code]=dbo.f_1(cid) from tb
/*
cid code
----------- ------------
1 01
2 02-01
3 03
4 04-02-01

（所影响的行数为 4 行）

*/

[wgsasd311]

@s=@s+'-'+code
=========>改为
@s=code+'-'+@s
就可以把显示的顺序倒过来了

[zjcxc]

USE tempdb
GO

--生成测试数据
create table BOM(CID INT,PID INT,CODE VARCHAR(10))
insert into BOM select 1,0,'01'
insert into BOM select 2,1,'02'
insert into BOM select 3,0,'03'
insert into BOM select 4,2,'04'
go

SELECT CID,PID,CODE=CAST(CODE as varchar(8000))
INTO #
FROM BOM
UPDATE A SET
CODE=P.CODE+'-'+A.CODE
FROM # P, # A
WHERE P.PID=0
AND P.CID=A.PID
WHILE @@ROWCOUNT>0
UPDATE A SET
CODE=P.CODE+'-'+A.CODE
FROM # P, # A
WHERE P.CID=A.PID
AND CHARINDEX('-',P.CODE)>0
AND CHARINDEX('-',A.CODE)=0
SELECT CID,CODE FROM #
DROP TABLE #
go

drop table BOM

/*--结果

CID CODE
----------- ------------
1 01
2 01-02
3 03
4 01-02-04

（所影响的行数为 4 行）
--*/

[zjcxc]

自定义函数的方法需要为每条记录跑一次它的所有父结点.

我的方法只需要对PID<>0的所有记录处理一次就行了.

如果你的子树嵌套较深, 用我的方法具有优势.

如果你的子树单纯是总记录数多, 嵌套的层次很浅,则应该考虑使用自定义函数的处理方式.(因为我的方法有临时表的开销, 不适宜于处理数据量特别大的表)

[zhang_yzy]

方法应该都大体相同吧,,
都需要用循环语句来连接赋值字符串吧