问;无限分类的sql语句的写法
有个表
id         belongToId
==         ================
1           2
2           3
3           4
4           5

怎样select的结果是1,2,3,4,5

也就是用户给个id是1...把属于他的所有的子id都给select出来

------解决方案--------------------
参考：

--生成测试数据
create table BOM(ID VARCHAR(10),PID VARCHAR(10))
insert into BOM select 'a ',NULL
insert into BOM select 'b ', 'a '
insert into BOM select 'c ', 'a '
insert into BOM select 'd ', 'b '
insert into BOM select 'e ', 'b '
insert into BOM select 'f ', 'c '
insert into BOM select 'g ', 'c '
go

--创建用户定义函数
create function f_getChild(@ID VARCHAR(10))
returns varchar(8000)
as
begin
declare @i int,@ret varchar(8000)
declare @t table(ID VARCHAR(10),PID VARCHAR(10),Level 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

select @ret = isnull(@ret, ' ')+ID from @t

return @ret
end
go

--执行查询
select ID,isnull(dbo.f_getChild(ID), ' ') from BOM group by ID
go

--输出结果
/*
a bcdefg
b de
c fg
d
e
f
g
*/

--删除测试数据
drop function f_getChild
drop table BOM
------解决方案--------------------
create table TESTTAB(
id int,
belongToId int
)
GO

insert into TESTTAB(id,belongToId)
select 1,2
UNION ALL select 2,3
UNION ALL select 3,4
UNION ALL select 4,5
GO

DECLARE @RESULTSTR VARCHAR(4000)
DECLARE @CUR_LV INT
DECLARE @LAST_LV INT

SELECT @CUR_LV=1 --此处为入口
SELECT @LAST_LV=0
SELECT @RESULTSTR=RTRIM(CAST (@CUR_LV AS CHAR(3)))
WHILE @LAST_LV <> @CUR_LV
BEGIN
SELECT @LAST_LV=@CUR_LV
SELECT @RESULTSTR=@RESULTSTR+ ', '+RTRIM(CAST (belongToId AS CHAR(3))),@CUR_LV=belongToId
FROM TESTTAB WHERE id=@CUR_LV
END

SELECT @RESULTSTR

---------------------
1,2,3,4,5

(1 行受影响)
------解决方案--------------------

CREATE FUNCTION GET_ALLLV(@LV INT)
returns varchar(8000)
as
BEGIN
DECLARE @RESULTSTR VARCHAR(4000)
DECLARE @CUR_LV INT
DECLARE @LAST_LV INT

SELECT @CUR_LV=@LV --此处为入口
SELECT @LAST_LV=-1
SELECT @RESULTSTR=RTRIM(CAST (@CUR_LV AS CHAR(3)))
WHILE @LAST_LV <> @CUR_LV
BEGIN
SELECT @LAST_LV=@CUR_LV
SELECT @RESULTSTR=@RESULTSTR+ ', '+RTRIM(CAST (belongToId AS CHAR(3))),@CUR_LV=belongToId
FROM TESTTAB WHERE id=@CUR_LV
END
RETURN @RESULTSTR
END
GO

SELECT id,dbo.GET_ALLLV(id) FROM TESTTAB

id
--------------------------------
1 1,2,3,4,5
2 2,3,4,5
3