查询分类并实现分类统计SQL存储过程

问题描述：

实现无限级查询和分类统计数据SQL存储过程。详细数据表设计如下：

设计的数据表
表一：PUb_Dict --用于存放分类表，理论上可以存放无限级，现有三级
DictId --编号
DictName --名称
ParentId --父类编号(根级用0表示)

另外一个表 pub_Info 信息引用这上面表DictId编号作为分类信息
表结构大致如下:
InfoID --自动编号
Infotitle --标题
InfoDate --时间
InfoContent --内容
DictId ---引用分类编号

表pub_Info 的分类可能同时存在 一级(如101)、二级(如101001)和三级(101001001)的分类数据
现在要分级统计各分类数量，同时在程序界面上显示分类名称、编号和统计结果.
另外要注意： 如果是父类的分类统计数据要包含子类的数量。

如：一级要包含二和三级，二级要包含三级。
分类为 101 的统计结果还要包含底下所有子分类的结果（如101001,101002,101003...)

 备注：因为服务器平台问题，数据库是SQL2000的

那么我实现方法如下：

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60

/**************************************************************************** *取得各种产品分类的ID，名称和统计条数 *创建时间：2010-09-21 *@rId:相当于父类编号（第一级为0） *@typeCode varchar(10), --子分类代码 *@tblName:表名 *@IdName:信息表引用的分类编号字段名称,如pub_Info表引用为(Res_Id) *@strWhere:附加过滤条件字符串，不用加关键词where,如:State=2，多个组合用AND拼接 调用例子：EXEC USP_Pub_GetPurveyResTypeCount 30102,'T','pub_info','Res_Id','State=2 AND Area_Id=10501' ************************************************************************************/ CREATE PROC USP_Pub_GetResTypeCount ( @rId int,--类别编号代表父类 @typeCode varchar(10), --子分类代码 @tblName varchar(50),--表名称 @IdName varchar(50),--ID列的名称 @strWhere varchar(200)--过滤条件 ) AS BEGIN   --创建临时表 CREATE TABLE #tt( [DictId]int primary key, [DictName] varchar(50), [Total] int ) DECLARE @i int,--索引号 @tCount int, --统计数据 @max int,--最大分类编号 @min int,--最小分类编号 @len int,--编号的长度 @dName varchar(50), --分类名称 @SQL nvarchar(1000),--查询SQL语句 @subLen int   IF(@typeCode='T') SET @subLen=2  --计算长度，因为有些是：10101001,有些是1010010001形式的 ELSE SET @subLen=3   SET @len=LEN(@rId) SET @min=(SELECT MIN(DictID) FROM  Pub_Dict WHERE ParentId=@rId AND typeCode=@typeCode) SET @max=(SELECT MAX(DictID) FROM  Pub_Dict WHERE ParentId=@rId AND typeCode=@typeCode)       SET @i=@min        WHILE(@i<=@max)      BEGIN                SET @SQL='SELECT @tCount=Count(1) FROM '+@tblName+' WHERE SUBSTRING(CAST('+@IdName+' AS VARCHAR(20)),1,CAST('+CAST(@len AS VARCHAR(10))+' AS INT))='+CAST(@i AS VARCHAR(20))+' AND '+@strWhere                EXECUTE SP_EXECUTESQL @SQL,N'@tCount INT OUTPUT',@tCount OUTPUT                   SET @dName=(SELECT DictName FROM Pub_Dict WHERE DictId=@i)        INSERT INTO #tt values(@i,@dName,@tCount)                 SET @i=@i+1      END   SELECT * FROM #tt --查询输出结果   DROP Table #tt --删除临时表   END   GO

虽然用这种方法能够较灵活实现了此功能，但每次调用都创建临时表，删除临时表，数据量大的时候，性能是一个值得思考的问题。但是到目前为止，想不到更好的办法，期待各位的解决方案。