Sql Server 中利用游标对table 的数据进行分组统计式输出…

   

Sql Server 中利用游标对table 的数据进行分组统计式输出…

 

 

Table Name: Tb_Color

Create table Tb_Color (id int identity(1,1),

                             Color varchar(20),

                             Qty Int)

 

ID Color   Qty

=============

1     Blue       10

2     Blue     11

3     Blue        12

4     Blue        13

5     Blue        14

6     Red        5

7     Red        6

8     Red        7

 

 

Write Store Procedure,

Procedure Table Name: Proc_Group_Total

 

Write Store Procedure,

Procedure Table Name: Proc_Group_Total

 

/* -------------------------------------------------- *

 * Procedure Name: Proc_Group_Total                  *

 *                                                   *

 * Author:Lilo.Zhu                                   *

 *                                                    *

 * Date:2006-09-28                                   *

 *                                                   *

 * Last Modified Date: 2006-09-28 10:44 AM           *

 *                                                   *

 * --------------------------------------------------*/

 

Create procedure Proc_Group_Total

as

declare @flag_Color varchar(20)

declare @Color varchar(20)

declare @qty int

declare @row_count int

declare @row_index int

declare @sum_qty int

declare @total_qty int

 

select @row_count=count(*) from tb_color

 

declare cursor_color cursor for

     select color,qty from tb_color

 

 

declare @color_table table(flag varchar(10),t_color varchar(20),t_qty int)

open cursor_color

 

     fetch next from cursor_color

            into @color,@qty

 

     set @flag_color=@color

 

set @row_index='0'

set @sum_qty='0'

set @total_qty='0'

 while @@fetch_status=0 and @row_index<@row_count

     begin

 

     if @flag_color<>@color

      begin

      insert into @color_table(flag,t_color,t_qty) values ('Sum',@flag_color,@sum_qty)

      set @flag_color=@color

      set @total_qty=@total_qty+@sum_qty

      set @sum_qty='0'

      end

 

     insert into @color_table(flag,t_color,t_qty) values ('-',@color,@qty)

     set @sum_qty=@sum_qty+@qty

 

 

       fetch next from cursor_color

              into @color,@qty

     set @row_index=@row_index+1

 

      if @row_index=@row_count

     begin

          insert into @color_table(flag,t_color,t_qty) values ('Sum',@flag_color,@sum_qty)

      set @flag_color=@color

      set @total_qty=@total_qty+@sum_qty

          insert into @color_table(flag,t_color,t_qty) values ('Total','-',@total_qty)

     end

     end

 

select * from @color_table

select * from tb_color

 

close cursor_color

deallocate cursor_color

 

执行Procedure

 

DECLARE @RC int

-- Set parameter values

EXEC @RC = [Study].[dbo].[Proc_Group_Total]

 

[F5]

 

Result :

Flag    Color    Qty

====================

-             Blue       10

-             Blue       11

-             Blue       12

-             Blue       13

-             Blue       14

Sum       Blue       60

-             Red        5

-             Red        6

-             Red        7

Sum       Red        18

Total       -             78

 

 

转载于:https://www.cnblogs.com/sesexxoo/archive/2006/09/28/6190381.html