求一SELECT 计算
表 t1

列 ta tb tc
数据 1 a 100
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

现在我们分别输入3个数 ta=1的 800 ta=2 的 100 ta=3的 100 查询出tx列和ty列

列 ta tb tc tx ty
数据 1 a 100 100 0
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 unionselect 1,'c',700 union select 2,'a',50 unionselect 2,'b',80 unionselect 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)unionselect * from Find(2,100)unionselect * from Find(3,100)1    a    100    100    01    b    300    300    01    c    700    400    3002    a    50    50    02    b    80    50    303    a    300    100    2003    b    70    70    0