---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