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

?

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 61 62 63 64 65 66 67 68 69 70 71

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