sql: T-SQL 统计计算(父子關係,樹形,分級分類的統計)
---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
哲学管理(学)人生, 文学艺术生活, 自动(计算机学)物理(学)工作, 生物(学)化学逆境, 历史(学)测绘(学)时间, 经济(学)数学金钱(理财), 心理(学)医学情绪, 诗词美容情感, 美学建筑(学)家园, 解构建构(分析)整合学习, 智商情商(IQ、EQ)运筹(学)生存.---Geovin Du(涂聚文)