1
CREATE
PROCEDURE
dbo.list_taoke
2 AS
3 /**/ /* SET NOCOUNT ON */
4 delete from taokee_Temp
5
6 declare @uid int ; /**/ /*用户ID*/
7 declare @exp_count decimal ; /**/ /*被淘的文章数量*/
8 declare @persent decimal ; /**/ /*被淘率*/
9 declare @info_count decimal ; /**/ /*发布的文章数量*/
10
11 /**/ /*以下循环*/
12 declare cursor_Userid cursor for
13 select author from info where (infolawid > 0 ) and ( exp >= 2 ) group by author
14 open cursor_Userid
15 begin
16 FETCH NEXT FROM cursor_Userid
17 INTO @uid
18 WHILE @@FETCH_STATUS = 0
19 BEGIN
20 set @exp_count = ( select count ( * ) from info where author = @uid and exp > 2 )
21 set @info_count = ( select count ( * ) from info where author = @uid )
22 set @persent = ( @exp_count / @info_count ) * 100
23 insert into taokee_Temp(uid,exp_count,persent,info_count) values ( @uid , @exp_count , @persent , @info_count )
24 FETCH NEXT FROM cursor_Userid
25 INTO @uid
26 END
27 end
28 close cursor_Userid
29 deallocate cursor_Userid
30 RETURN
31
32 GO
33
2 AS
3 /**/ /* SET NOCOUNT ON */
4 delete from taokee_Temp
5
6 declare @uid int ; /**/ /*用户ID*/
7 declare @exp_count decimal ; /**/ /*被淘的文章数量*/
8 declare @persent decimal ; /**/ /*被淘率*/
9 declare @info_count decimal ; /**/ /*发布的文章数量*/
10
11 /**/ /*以下循环*/
12 declare cursor_Userid cursor for
13 select author from info where (infolawid > 0 ) and ( exp >= 2 ) group by author
14 open cursor_Userid
15 begin
16 FETCH NEXT FROM cursor_Userid
17 INTO @uid
18 WHILE @@FETCH_STATUS = 0
19 BEGIN
20 set @exp_count = ( select count ( * ) from info where author = @uid and exp > 2 )
21 set @info_count = ( select count ( * ) from info where author = @uid )
22 set @persent = ( @exp_count / @info_count ) * 100
23 insert into taokee_Temp(uid,exp_count,persent,info_count) values ( @uid , @exp_count , @persent , @info_count )
24 FETCH NEXT FROM cursor_Userid
25 INTO @uid
26 END
27 end
28 close cursor_Userid
29 deallocate cursor_Userid
30 RETURN
31
32 GO
33
更新于: 2006年11月22日 下午
发现上面的存储过程可以被一句SQL取代,如下:
select
top
50
*
from
(
select distinct author as uid ,info_count = ( select count ( 0 ) from info where info.author = t.author ),
exp_count = ( select count ( 0 ) from info where info.author = t.author and exp > 2 )
from info t where infolawid > 0
) as x order by x.exp_count desc
(
select distinct author as uid ,info_count = ( select count ( 0 ) from info where info.author = t.author ),
exp_count = ( select count ( 0 ) from info where info.author = t.author and exp > 2 )
from info t where infolawid > 0
) as x order by x.exp_count desc