---sql: T-SQL 统计计算（父子關係,樹形，分級分類的統計）
---2014-08-26 塗聚文(Geovin Du)
CREATE PROCEDURE proc_Select_BookKindSumReport
AS
DECLARE @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 int
DECLARE @tempId INT ,
    @tempName VARCHAR(10), 
    @tempParent int
drop table #temp
select * into #temp from BookKindList
WHILE 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)
END
select * from @temp order by BookKindParent
GO



--利用游标来遍历表
--定义表变量
DECLARE @temp TABLE
(
      BookKindID INT IDENTITY(1, 1) ,
      BookKindName  VARCHAR(10),
      BookKindParent int
)  

DECLARE @tempId INT ,
    @tempName VARCHAR(10),
	@tempParent int
DECLARE test_Cursor CURSOR LOCAL FOR
SELECT   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_Cursor
WHILE @@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_Cursor
DEALLOCATE test_Cursor