额 就是我先说下第一种情况这个我会 select f2.id,f2.cof2,f2.num from f1 inner join f2 on f1.co1=f2.cof2 where f2.num=@num (@num 是我第一的一个游标的传递值---我想利用游标,但是好像有人提供纯select 只是我第二步做不出来! 哎 ) 麻烦各位在帮帮忙了,谢谢啦!
调试欢乐多
要这样的格式
id Uid co num
1 2 a 50
2 3 b 30
3 4 b 50
4 6 b 20
5 3 c 71
6 4 d 58
红色的 是我自动用select identity(int,1,1) as id ,……生成的一个自增id, 而最后两行是由于f1表中不能查找到符合条件的值,就还是带F1的原字段,这样描述能清楚吗?
比如还有B 40,B 70...
http://topic.csdn.net/u/20080415/13/78dc8b89-59bb-4c4b-a921-5483cec6471a.html?seed=2044235486
1 2 a 50
2 3 b 30
3 4 b 50
4 6 b 20
5 7 c 71
6 9 d 50
7 10 d 3 应该是红色的
insert @f1 select
1 ,'a', 50 union select
2 ,'b', 100 union select
3 ,'c', 71 union select
4 ,'d', 58
declare @f2 table( id int,cof2 varchar(10),num int)
insert @f2 select
1 ,'a', 20 union select
2 ,'a', 50 union select
3 ,'b', 30 union select
4 ,'b', 50 union select
5 ,'b', 10 union select
6 ,'b', 20 union select
7 ,'c', 80 union select
8 ,'c', 14 union select
9 ,'d', 50 union select
10 ,'d', 3 select px = identity(int,1,1) ,id,cof2,num into # from @f2 order by cof2,num descselect id,cof2,num
from(
select
a.*,结存=(b.num1-isnull((select sum(num) from # where cof2 = a.cof2 and px<a.px),0))
from
# a,@f1 b
where a.cof2 = b.co1 ) c
where c.结存 >0
drop table #/*id cof2 num
----------- ---------- -----------
2 a 50
4 b 50
3 b 30
6 b 20
7 c 80
9 d 50
10 d 3(所影响的行数为 7 行)
*/
insert @f1 select
1 ,'a', 50 union select
2 ,'b', 100 union select
3 ,'c', 71 union select
4 ,'d', 58
declare @f2 table( id int,cof2 varchar(10),num int)
insert @f2 select
1 ,'a', 20 union select
2 ,'a', 50 union select
3 ,'b', 30 union select
4 ,'b', 50 union select
5 ,'b', 10 union select
6 ,'b', 20 union select
7 ,'c', 80 union select
8 ,'c', 14 union select
9 ,'d', 50 union select
10 ,'d', 3 select px = identity(int,1,1) ,id,cof2,num into # from @f2 order by cof2,num descselect id,cof2,case when num>num1 then num1 else num end as num
from(
select
a.*,b.num1,结存=(b.num1-isnull((select sum(num) from # where cof2 = a.cof2 and px<a.px),0))
from
# a,@f1 b
where a.cof2 = b.co1 ) c
where c.结存 >0
drop table #/*id cof2 num
----------- ---------- -----------
2 a 50
4 b 50
3 b 30
6 b 20
7 c 71
9 d 50
10 d 3(所影响的行数为 7 行)
*/
----------- ---------- -----------
2 a 50
4 b 50
3 b 30
6 b 20
7 c 71
9 d 50
10 d 3 --f1表中可是58 ,应该扣不完的呀
(所影响的行数为 7 行)
*/