求一SELECT 计算,该如何处理

www.myexceptions.net  网友分享于：2013-04-28  浏览：11次

1 b 300
1 c 700
2 a 50
2 b 80
3 a 300
3 b 70

ta =1 的 tc 合计1100
ta =2 的 tc 合计130
ta =3 的 tc 合计370

1 b 300 300 0
1 c 700 500 200
2 a 50 50 0
2 b 80 50 30
3 a 300 100 200
3 b 70 0 70

------解决方案--------------------
SQL code
```
create table t(ta int ,tb char(1),tc int)
insert into t
select 1,'a',100 union
select 1,'b',300 union
select 1,'c',700 union
select 2,'a',50 union
select 2,'b',80 union
select 3,'a',300 union
select 3,'b',70

create  Function Find(@ta int,@m int)
returns  @r table(ta int ,tb char(1),tc int,tx int,ty int)
as
begin
DECLARE TCURSOR CURSOR local FOR
select ta,tb,tc from t where ta=@ta;
declare @v_ta int ,@v_tb char(1),@v_tc int;
OPEN TCURSOR;
FETCH NEXT FROM TCURSOR INTO @v_ta,@v_tb,@v_tc;
WHILE @@FETCH_STATUS=0
BEGIN
if(@m>=@v_tc)
begin
insert @r select @v_ta,@v_tb,@v_tc,@v_tc,0;
set @m=@m-@v_tc;
end
else
begin
insert @r select @v_ta,@v_tb,@v_tc,@m,@v_tc-@m;
end
FETCH NEXT FROM TCURSOR INTO @v_ta,@v_tb,@v_tc;
END
CLOSE TCURSOR;
DEALLOCATE TCURSOR;
return ;
end

select * from Find(1,800)
union
select * from Find(2,100)
union
select * from Find(3,100)

1    a    100    100    0
1    b    300    300    0
1    c    700    400    300
2    a    50    50    0
2    b    80    50    30
3    a    300    100    200
3    b    70    70    0```