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 |
哲学管理(学)人生, 文学艺术生活, 自动(计算机学)物理(学)工作, 生物(学)化学逆境, 历史(学)测绘(学)时间, 经济(学)数学金钱(理财), 心理(学)医学情绪, 诗词美容情感, 美学建筑(学)家园, 解构建构(分析)整合学习, 智商情商(IQ、EQ)运筹(学)生存.---Geovin Du(涂聚文)
【推荐】编程新体验,更懂你的AI,立即体验豆包MarsCode编程助手
【推荐】凌霞软件回馈社区,博客园 & 1Panel & Halo 联合会员上线
【推荐】抖音旗下AI助手豆包,你的智能百科全书,全免费不限次数
【推荐】博客园社区专享云产品让利特惠,阿里云新客6.5折上折
【推荐】轻量又高性能的 SSH 工具 IShell:AI 加持,快人一步