无限级分类问题

[php_wsd]

以前产品分类表的结构
ID cname(分类名称) parendid（父ID）
1 A 0
2 B 0
3 a 1
4 01 3
5 b 2
6 02 3
在显示的时候，我使用了递归将分类名称显示出来的，现在发现这样非常影响速度，想改成这样的结构
ID cname(分类名称) parendid（父ID）path
1 A 0 0
2 B 0 0
3 a 1 0,1
4 01 3 0,1,3
5 b 2 0,2
6 02 3 0,1,3
有没有办法通过sql语句将上面中的数据转成下面的结构中的数据？

[guguda2008]

USE TEMPDB
GO
IF OBJECT_ID('TB') IS NOT NULL DROP TABLE TB
GO
CREATE TABLE TB(
ID INT
,CNAME VARCHAR(10)
,PARENDID INT
)
INSERT INTO TB
SELECT 1 ,'A', 0 UNION ALL
SELECT 2 ,'B', 0 UNION ALL
SELECT 3 ,'a', 1 UNION ALL
SELECT 4 ,'01', 3 UNION ALL
SELECT 5 ,'b', 2 UNION ALL
SELECT 6 ,'02', 3

;WITH MU AS (
SELECT ID,CNAME,PARENDID,CONVERT(VARCHAR(MAX),'0') AS [PATH] FROM TB WHERE PARENDID=0
UNION ALL
SELECT 
T1.ID,T1.CNAME,T1.PARENDID,T2.[PATH]+','+LTRIM(T2.ID)
FROM TB T1
INNER JOIN MU T2 ON T1.PARENDID=T2.ID
)

SELECT * FROM MU
ORDER BY ID
/*
ID          CNAME      PARENDID    PATH
----------- ---------- ----------- ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
1           A          0           0
2           B          0           0
3           a          1           0,1
4           01         3           0,1,3
5           b          2           0,2
6           02         3           0,1,3
*/

[-晴天]

递归.

[正牌风哥]

SQL2008用
hierarchyid

[php_wsd]

谢谢，这下轻松多了

[Xiao_Ai_Mei]

奇怪了，我的和鸭子的，我看好像没什么区别嘛，怎么我的报错啊？？


--> 测试数据: #tb
if object_id('tb') is not null drop table tb
go 
create table tb (ID int,cname varchar(2),parendid int)
insert into tb
select 1,'A',0 union all
select 2,'B',0 union all
select 3,'a',1 union all
select 4,'01',3 union all
select 5,'b',2 union all
select 6,'02',3

;with cte as
(
	select ID,CNAME,PARENDID, cast(parendid as varchar(100)) as p from tb where parendid=0
	union all
	select a.id,A.cname,a.parendid, t.p+','+LTRIM(a.parendid)
	from tb a 
	join cte t 	on a.parendid =t.ID 
)
select * from cte 

 

--Types don't match between the anchor and the recursive part in column "p" of recursive query "cte".

[Rotel-刘志东]

递归问题