想不出来请大家多多指教!!

[wendyc]

aid qty
a 1000
b 200
c 600

table b
aid qty bid
a 200 1
a 900 2
a 200 3
b 100 1
c 700 1
......
我想要这样的效果
a 1000 200 1
a 1000 900 2
b 200 100 1
c 600 700 1

是一个库存的问题,b 表的总和是库存 但是查询出来只要上面的和(200+900>1000)就不显示下面的,如果不足全部都显示

想了半天 请大家多多指教!!

[wendyc]

自己先顶,今天下班了 明天再看!!

[lizhizhe2000]

Creat View myview AS
SELECT ....
FROM a,b
WHERE a.aid=b.bid

[userqin]

create table a
(
id varchar(2) primary key,
qty int
)

insert into a select 'a',1000
union all select 'b',200
union all select 'c',600

select * from a
create table b
(
aid varchar(2) ,
qty int,
bid int
)


insert into b select 'a',200,1
union all select 'a',900,2
union all select 'a',200,3
union all select 'b',100,1
union all select 'c',700,1

select b1.aid,a1.qty ,b1.qty,b1.bid from a a1,(select aid,qty,min(bid) as bid from b group by aid,qty ) b1 where a1.id=b1.aid order by b1.aid

[wendyc]

我查询的结果把所有的都显示出来了 好象不对,是以什么样的限制条件让 a 200 3不显示的呢??

[chenjunjarysky]

学习了哦！

[userqin]

你给的查询结果中没有 a 200 3 这一行，
使用了group by aid,qty

[userqin]

lz要的是这样的结果吗？

aid a.qty b.qty bid
a 1000 200 1
a 1000 900 2
a 1000 200 3
b 200 100 1
c 600 700 1



select b.aid,a.qty,b.qty,b.bid from a ,b where a.id=b.aid

[zhang_yzy]

select
id,
aqty,
bqty,
bid
from
(
select
a.id ,
a.qty as aqty,
b.qty as bqty,
b.bid,
isnull((select sum(qty) from b c where aid =b.aid and bid<b.bid),0) as sum1
from
a left join b on a.id=b.aid
) e
where
aqty >sum1

[wendyc]

大家 谢谢了 问题解决了；