抄zjcxc大神的一个方法 CREATE FUNCTION f_cid(@FileClassid int) RETURNS TABLE AS RETURN( WITH tb_tree([FileClassid],[level]) AS( SELECT [FileClassid],1 FROM [tb_tree] WHERE [fid]=@id UNION ALL SELECT A.[FileClassid],B.[level]+1 FROM [表名] A,tb_tree B WHERE A.[fid]=B.[FileClassid]) SELECT * FROM tb_tree ) go select classname,s,fileclassid,fid, stuff(( SELECT ','+a.详细信息 FROM [表名] A,f_cid(c.FileClassid)B WHERE A.[FileClassid]=B.[FileClassid] for xml path('')),1,1,'') as 详细信息 from [表名] c
f_cid 重改下CREATE FUNCTION f_cid(@id int) RETURNS TABLE AS RETURN( WITH tb_tree([FileClassid],[level]) AS( SELECT [FileClassid],0 FROM [表名] WHERE [id]=@id UNION ALL SELECT [FileClassid],1 FROM [表名] WHERE [fid]=@id UNION ALL SELECT A.[FileClassid],B.[level]+1 FROM [表名] A,tb_tree B WHERE A.[fid]=B.[FileClassid]) SELECT * FROM tb_tree )
CREATE FUNCTION f_cid(@FileClassid int)
RETURNS TABLE
AS
RETURN(
WITH tb_tree([FileClassid],[level])
AS(
SELECT [FileClassid],1 FROM [tb_tree]
WHERE [fid]=@id
UNION ALL
SELECT A.[FileClassid],B.[level]+1
FROM [表名] A,tb_tree B
WHERE A.[fid]=B.[FileClassid])
SELECT * FROM tb_tree
)
go
select classname,s,fileclassid,fid,
stuff((
SELECT ','+a.详细信息
FROM [表名] A,f_cid(c.FileClassid)B
WHERE A.[FileClassid]=B.[FileClassid]
for xml path('')),1,1,'') as 详细信息
from [表名] c
RETURNS TABLE
AS
RETURN(
WITH tb_tree([FileClassid],[level])
AS(
SELECT [FileClassid],0 FROM [表名]
WHERE [id]=@id
UNION ALL
SELECT [FileClassid],1 FROM [表名]
WHERE [fid]=@id
UNION ALL
SELECT A.[FileClassid],B.[level]+1
FROM [表名] A,tb_tree B
WHERE A.[fid]=B.[FileClassid])
SELECT * FROM tb_tree
)
一个表
自己的id和子ID
用后面的
前面的我写错了