sql: T-SQL 统计计算（父子關係,樹形，分級分類的統計）

---sql: T-SQL 统计计算（父子關係,樹形，分級分類的統計）---2014-08-26 塗聚文(Geovin Du)CREATE PROCEDURE proc_Select_BookKindSumReportASDECLARE @temp TABLE(      BookKindID INT IDENTITY(1, 1) ,      BookKindName  VARCHAR(10),      BookKindParent int,  BookKindSum int )--declare @id int,@grouid nvarchar(500),@sql nvarchar(4000),@cstucount int,@c intDECLARE @tempId INT ,    @tempName VARCHAR(10),     @tempParent intdrop table #tempselect * into #temp from BookKindListWHILE EXISTS ( SELECT   BookKindID FROM   #temp )    BEGIN          SET ROWCOUNT 1           SELECT  @tempId = [BookKindID] ,                @tempName = [BookKindName],@tempParent=BookKindParent        FROM    #temp          SET ROWCOUNT 0  --delete from #temp where BookKindID = @tempId         set @id=@tempId        select  @grouid=dbo.GetBookKindGroupId (@id)select @sql='SELECT @c=count(*) FROM BookInfoList where BookInfoKind in ('+@grouid+')'--exec (@sql)exec sp_executesql @sql,N'@c int output',@cstucount output--将exec的结果放入变量中的做法 --select @cstucount  as 'sum'           --PRINT '记录:----'+ cast(@tempId as varchar(20))+',' + @tempName+','+ cast(@tempParent as varchar(20))+' sum:'+ cast(@cstucount as varchar(50))insert into @temp(BookKindName,BookKindParent,BookKindSum) values(@tempName,@tempParent,@cstucount)ENDselect * from @temp order by BookKindParentGO--利用游标来遍历表--定义表变量DECLARE @temp TABLE(      BookKindID INT IDENTITY(1, 1) ,      BookKindName  VARCHAR(10),      BookKindParent int)  DECLARE @tempId INT ,    @tempName VARCHAR(10),@tempParent intDECLARE test_Cursor CURSOR LOCAL FORSELECT   BookKindID,BookKindName,BookKindParent FROM @temp--插入数据值INSERT  INTO @temp VALUES  ( 'a',1 ) INSERT  INTO @temp VALUES  ( 'b',2 )INSERT  INTO @temp VALUES  ( 'c',3 )INSERT  INTO @temp VALUES  ( 'd',4 ) INSERT  INTO @temp VALUES  ( 'e',5 ) --打开游标OPEN test_CursorWHILE @@FETCH_STATUS = 0    BEGIN          FETCH NEXT FROM test_Cursor INTO @tempId,@tempname,@tempParent        PRINT '记录:----' + cast(@tempId as varchar(20))+',' + @tempName+','+ cast(@tempParent as varchar(20))      END CLOSE test_CursorDEALLOCATE test_Cursor