首页 > 代码库 > sql: T-SQL 统计计算(父子關係,樹形,分級分類的統計)
sql: T-SQL 统计计算(父子關係,樹形,分級分類的統計)
---sql: T-SQL 统计计算(父子關係,樹形,分級分類的統計)---2014-08-26 塗聚文(Geovin Du)CREATE PROCEDURE proc_Select_BookKindSumReportASDECLARE @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 intDECLARE @tempId INT , @tempName VARCHAR(10), @tempParent intdrop table #tempselect * into #temp from BookKindListWHILE 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)ENDselect * from @temp order by BookKindParentGO--利用游标来遍历表--定义表变量DECLARE @temp TABLE( BookKindID INT IDENTITY(1, 1) , BookKindName VARCHAR(10), BookKindParent int) DECLARE @tempId INT , @tempName VARCHAR(10), @tempParent intDECLARE test_Cursor CURSOR LOCAL FORSELECT 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_CursorWHILE @@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_CursorDEALLOCATE test_Cursor
sql: T-SQL 统计计算(父子關係,樹形,分級分類的統計)
声明:以上内容来自用户投稿及互联网公开渠道收集整理发布,本网站不拥有所有权,未作人工编辑处理,也不承担相关法律责任,若内容有误或涉及侵权可进行投诉: 投诉/举报 工作人员会在5个工作日内联系你,一经查实,本站将立刻删除涉嫌侵权内容。