Sql Server中利用游标对table的数据进行分组统计式输出…Table Name:Tb_ColorCreate tableTb_Color(id int identity(1,1), Color varchar(20), Qty Int) IDColor Qty ============= 1 Blue 10 2 Blue 11 3 Blue 12 4 Blue 13 5 Blue 14 6 Red 5 7 Red 6 8 Red 7Write Store Procedure,Procedure Table Name:Proc_Group_TotalWrite Store Procedure, Procedure Table Name: Proc_Group_Total /* -------------------------------------------------- * *Procedure Name: Proc_Group_Total * * * *Author:Lilo.Zhu * * * *Date:-09-28 * * * *Last Modified Date: -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 14Sum Blue 60- Red 5- Red 6- Red 7Sum Red 18Total - 78
