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

超、凢脫俗 2021-12-13 00:51 157阅读 0赞

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

转载于:https://www.cnblogs.com/geovindu/p/3937046.html