表 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 [email protected]; 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>[email protected]_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,@[email protected]; 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