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
郑重声明:本站内容如果来自互联网及其他传播媒体,其版权均属原媒体及文章作者所有。转载目的在于传递更多信息及用于网络分享,并不代表本站赞同其观点和对其真实性负责,也不构成任何其他建议。