关于无限级分类的问题～

[xf2gl]

现有物资类别表m_class，结构如下：


guid superior_guid class_name
1 1 办公用品
2 1 笔类
3 2 毛笔
4 2 钢笔


理论上可以支持无限级分类，请问各位高手，怎样用最高的效率读出各级分类的GUID，有多少级别就要读出多少，并存入一张临时表里面，谢谢～

[yelook]

http://xiangbj.vicp.net/Forums/ShowPost.aspx?PostID=9#9

[xf2gl]

我用的GUID数据类型为字符型的（例：CBAA7F41-BB4C-3F0229FED5D1），这样的化用你的存储过程就不行，还望高手帮我再改改！

[davorsuker39]

学习

[yelook]

CREATE PROCEDURE GetIDS
(
@PID int
)
AS
DECLARE
@higherID int,
@intCount int
set @higherID = @PID
declare @tb_Result TABLE(ID int)
declare @tb_Parent TABLE(ID int)
declare @tb_Child TABLE(ID int)
INSERT INTO @tb_Parent(ID) VALUES (@higherID)
SELECT @intCount = COUNT(*)
FROM [UserTable]
WHERE (PID = @higherID)
WHILE @intCount <> 0 -- 有下级
BEGIN
DELETE @tb_Child /* 清空子表 */
INSERT INTO @tb_Child(ID)
SELECT ID FROM [UserTable]
WHERE (PID in (SELECT ID FROM @tb_Parent))
SELECT @intCount = COUNT(*) FROM @tb_Child
INSERT INTO @tb_Result(ID) SELECT ID FROM @tb_Child
DELETE FROM @tb_Parent
INSERT INTO @tb_Parent(ID) SELECT ID FROM @tb_Child
END
insert into @tb_Result(ID) values (@higherID)