这样的UPDATE SET 可以实现吗...好像很有难度.....

[amryecq]

一张表..本来只有ID,ParentID字段
如这样:
ID ParentID
1 null
2 null
3 1
4 3
5 4
....
现在加了一个字段 PIDs
想通过一条UPDATE 语句来实现下面这种结果...
ID ParentID PIDs
1 null null
2 null null
3 1 1
4 3 1,3
5 4 1,3,4
....

能否...不过也应该很基础地吧...?

[pt1314917]

--> 测试数据: [s]
if object_id('[s]') is not null drop table [s]
create table [s] (ID int,ParentID int)
insert into [s]
select 1,null union all
select 2,null union all
select 3,1 union all
select 4,3 union all
select 5,4
go

create proc wsp
as
	select *,cast(null as varchar(50)) pids  into #os from s
	DECLARE @i int,@j int
	set @i=0
	set @j=1
	select @i=max(ParentID) from #os
	while @j<=@i
	begin
		   update #os set pids=isnull(a.pids+',','')+ltrim(#os.parentid) 
				from #os inner join #os a on #os.parentid=a.id 
		   where #os.parentid=@j 
		   set @j=@j+1
	end
	select * from #os
go

exec wsp

[让你望见影子的墙]

--> (让你望见影子的墙)生成测试数据，时间：2009-03-08
 
if not object_id('tb') is null
	drop table tb
Go
Create table tb([ID] int,[ParentID] varchar(10))
Insert tb
select 1,null union all
select 2,null union all
select 3,1 union all
select 4,3 union all
select 5,4
Go
Select * from tb
create function f_getchar(@id int)
returns varchar(100)
as
begin
declare @s varchar(100)
set @s=''
select @s=@s+isnull(ParentID,'')+','
from tb
where id<=@id

return @s
end

update tb
set pids=dbo.f_getchar(id)

select * from tb

1	NULL	,
2	NULL	,,
3	1	,,1,
4	3	,,1,3,
5	4	,,1,3,4,

类似如此，你再稍微处理一下

[htl258_Tony]

if object_id('tb') is not null drop table tb
go
create table tb(ID int, ParentID int)
insert tb
select 1 , null union all
select 2 , null union all 
select 3 , 1 union all 
select 4 , 3 union all 
select 5 , 4 

alter table tb add  PIDs varchar(200)

declare @i varchar(200)

update tb set pids=stuff(@i,1,1,''),
              @i=isnull(@i,'')+','+ltrim(ParentID)

select * from tb


drop table tb

ID          ParentID    PIDs
----------- ----------- --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
1           NULL        NULL
2           NULL        NULL
3           1           1
4           3           1,3
5           4           1,3,4

(5 行受影响)