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