记录一下吧
代码语言：javascript复制 1 USE [NPU_Group]
2 GO
3 /****** Object: StoredProcedure [dbo].[Proc_HotAndFutrueKeywords] Script Date: 02/05/2013 10:02:26 ******/
4 SET ANSI_NULLS ON
5 GO
6 SET QUOTED_IDENTIFIER ON
7 GO
8 -- ================================================
9 -- Proc Function : 研究热点和趋势统计存储过程
10 -- Create Date : 2013-02-01
11 -- Update Date : 2012-02-01
12 -- Create User : IsaacZhang
13 -- Update User : IsaacZhang
14 -- Description : 研究热点和趋势统计数据
15 -- Parameter :
16 -- ================================================
17 ALTER PROCEDURE [dbo].[Proc_HotAndFutrueKeywords]
18 @year INT ,--年度
19 @groupIntId INT, --群组ID
20 @TopNum INT --取多少条热点数据
21 AS
22
23
24 --创建结果临时表
25 CREATE TABLE #tbResult(
26 KeywordIdInt INT ,
27 KeywordId UNIQUEIDENTIFIER ,
28 KeyWordName VARCHAR(200) ,
29 ForeignName VARCHAR(200) ,
30 IsTopic BIT ,
31 HotYear INT,
32 TotalCount decimal(18, 2),
33 GroupIntId INT,
34 AmplificationNum decimal(18, 2),
35 Amplification decimal(18, 2),
36 AmpliFactor decimal(18, 2)
37 )
38 --定义要输出的所有年份
39 DECLARE @YearCount INT
40 SET @YearCount = 10;
41 --声明变量
42 DECLARE @KeywordIdInt INT
43 DECLARE @KeywordId UNIQUEIDENTIFIER
44 DECLARE @KeyWordName VARCHAR(200),@ForeignName VARCHAR(200)
45 DECLARE @IsTopic BIT
46 DECLARE @TotalCount decimal(18, 2)
47 DECLARE @Amplification decimal(18, 2),@lastYearCount decimal(18, 2),@AmplificationNum decimal(18, 2)
48 ,@AmpliFactor decimal(18, 2)
49
50 DECLARE @CurrentYear INT
51 --如果群组ID不为0，则取全部关键词文献信息
52 IF @groupIntId!=0
53 BEGIN
54
55 BEGIN TRANSACTION Trans_HotAndFutrueKeywords --创建事务
56 --删除表中原数据
57 DELETE FROM dbo.Pt_FutureAmplification;
58
59 --初始化当前年
60 SET @CurrentYear = @year;
61 WHILE(@CurrentYear>=@year-@YearCount)
62 BEGIN
63 --声明游标
64 DECLARE hotKeywordsIndex CURSOR
65 FOR SELECT * FROM NPU_Core.dbo.Keyword
66 OPEN hotKeywordsIndex
67 FETCH NEXT FROM hotKeywordsIndex INTO @KeywordIdInt,@KeywordId,@KeyWordName
68 ,@ForeignName,@IsTopic
69 WHILE @@FETCH_STATUS = 0
70 BEGIN
71 --每次重新赋值
72 SET @TotalCount = 0;
73 SET @lastYearCount = 0;
74 PRINT '==============' CAST(@CurrentYear AS VARCHAR(20)) '==============='
75 SELECT @TotalCount = COUNT(0) FROM View_ShareBib2Keywords WHERE KeywordIdInt =@KeywordIdInt
76 AND [Year] = @CurrentYear
77
78 SELECT @lastYearCount = COUNT(0) FROM View_ShareBib2Keywords WHERE KeywordIdInt =@KeywordIdInt
79 AND [Year] = (@CurrentYear-1)
80 --PRINT CAST(@TotalCount AS VARCHAR(20)) '<-current-------last->' CAST(@lastYearCount AS VARCHAR(20))
81 --增长数量
82 SET @AmplificationNum = @TotalCount-@lastYearCount;
83 --增长幅度
84 IF @lastYearCount!=0 AND @lastYearCount!=1
85 BEGIN
86 SET @Amplification = (@TotalCount-@lastYearCount)/@lastYearCount
87 --增长因素
88 SET @AmpliFactor = @AmplificationNum*@Amplification;
89 --如果增幅为负增长
90 IF(@TotalCount<@lastYearCount)
91 BEGIN
92 SET @Amplification = -round((@lastYearCount-@TotalCount)/@lastYearCount,3);
93 SET @AmpliFactor = -@AmpliFactor;
94 --PRINT '|||' CAST(@Amplification AS VARCHAR(20))
95 END
96 END
97 ELSE
98 BEGIN
99 SET @Amplification = (@TotalCount-@lastYearCount)/1.00;
100 SET @AmpliFactor = 0;
101 END
102 IF @TotalCount IS NULL
103 SET @TotalCount = 0;
104
105 INSERT INTO Pt_FutureAmplification VALUES(
106 @KeywordIdInt,
107 @KeywordId,
108 @KeyWordName,
109 @ForeignName,
110 @IsTopic,
111 @CurrentYear,
112 @TotalCount,
113 @groupIntId,
114 @AmplificationNum,
115 @Amplification,
116 @AmpliFactor
117 );
118 --执行下一次游标操作
119 FETCH NEXT FROM hotKeywordsIndex INTO @KeywordIdInt,@KeywordId,@KeyWordName
120 ,@ForeignName,@IsTopic
121 END
122 --关闭游标
123 CLOSE hotKeywordsIndex
124 --释放资源
125 DEALLOCATE hotKeywordsIndex
126 SET @CurrentYear = @CurrentYear-1;
127 END
128
129 SELECT top ((@TopNum)) * FROM #tbResult
130 WHERE HotYear = ((@year-2))
131 ORDER BY TotalCount DESC,HotYear DESC
132 IF @@ERROR=0
133 COMMIT TRANSACTION Pt_FutureAmplification -- 提交事务处理
134 ELSE
135 ROLLBACK TRANSACTION Pt_FutureAmplification --事务回滚
136 END
