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

 

posted @   ®Geovin Du Dream Park™  阅读(794)  评论(0编辑  收藏  举报
努力加载评论中...
点击右上角即可分享
微信分享提示