sql数据库两表查询问题

[panxinxin1987]

asp.nt中
我这里有两个表:
入库表A:
it_id it_qty(数量)
1 50
2 70

出库表B ot_id ot_qty（数量） it_id
001 40 1
002 20 2
怎么把表A与表B相连之后每一行的入库数量(it_qty)减去出库数量(ot_qty),显示剩余数量
如果剩余数量为0,则这一条数据不会显示出来。用GridView绑定出来。求各位帮帮忙啊

[panxinxin1987]

如果剩余数量为0,则这一条数据不会显示出来。提示一下：这两个表在数据库中有，而且有数据，我只是想要各位帮我写sql语句而已。特别是 如果剩余数量为0,则这一条数据不会显示出来 有谁不用存储过程写的方法啊，摆脱各位了

[mbh0210]

我4楼的不行吗？

[panxinxin1987]

行啊，我只是不习惯在存储过程里写啊。

[zhangrenying]

 select a.it_id,mim(a.it_qty)-SUM(b.ot_qty) as '剩余的' from a inner join b 
 on  a.it_id =b.it_id  
   group by a.it_id  
having (MIN(a.it_qty)-SUM(b.ot_qty)>0)

[panxinxin1987]

select a.it_id , min(a.it_qty) -SUM(b.ot_qty) as qty from @t a中的"qty"是什么意思啊

[指间的风]

发到sql区，哪里解决这个是小case

[不懂装懂]

select *
from
(
select tab_1.*,t1.it_qty
from
(
select t2.it_id,sum(t2.ot_qty)as ot_qty
from Table_2 t2
group by t2.it_id
)as tab_1,Table_1 t1
where tab_1.it_id=t1.it_id
)as tab_2
where tab_2.it_id=tab_2.it_id and tab_2.it_qty>tab_2.ot_qty

[不懂装懂]

上面少点东西...
select tab_2.*,(tab_2.it_qty-tab_2.ot_qty)as qty
from
(
select tab_1.*,t1.it_qty
from
(
select t2.it_id,sum(t2.ot_qty)as ot_qty
from Table_2 t2
group by t2.it_id
)as tab_1,Table_1 t1
where tab_1.it_id=t1.it_id
)as tab_2
where tab_2.it_id=tab_2.it_id and tab_2.it_qty>tab_2.ot_qty

[panxinxin1987]

select TabInstore_Total.it_pno,TabInstore_Total.it_qty,TabInstore_Total.it_nw,TabOutstore_Total.ot_qty,TabOutstore_Total.ot_sdate,min( TabInstore_Total.it_qty)-SUM( TabOutstore_Total.ot_qty) as '剩余的' from TabInstore_Total inner joinTabOutstore_Total on TabInstore_Total.it_batch=TabOutstore_Total.ot_batch and TabInstore_Total.it_pno=TabOutstore_Total.ot_pno order by TabInstore_Total.it_pno
group by TabInstore_Total.it_batch
having (min( TabInstore_Total.it_qty)-SUM( TabOutstore_Total.ot_qty))>0

它的提示“on”附近有语法错误啊

[panxinxin1987]

12楼：

select tab_2.*,(tab_2.it_qty-tab_2.ot_qty)as qty
tab_2.it_qty-tab_2.ot_qty ????
“it_qty ” 和 “ot_qty ” 它们不在同一个表里啊

[mbh0210]

一对多还是一对一？

[ws_hgo]

  it_id int,
  it_qty int
)
insert into #A select 1,50
insert into #A select 2,70

create table #B
(
 ot_id nchar(10),
 ot_qty int,
 it_id int 
)
insert into #B select '001',40,1
insert into #B select '002',20,2

select b.it_id,(A.it_qty-B.ot_qty) '剩余的'
from #A A join #B B
on A.it_id=b.it_id

it_id       剩余的
----------- -----------
1           10
2           50

[xray2005]

SQL 语句：

select a.it_id,a.it_qty-b.ot_qty,b.ot_id
from a inner join b on a.it_id=b.it_id order by a.it_id;

然后按正常代码执行，绑定到GRIDVIEW就可以了

[mbh0210]

declare @t table( it_id  int , it_qty int )
   insert into @t values( 1   ,   50 )
   insert into @t values( 2   ,  70  )
   
   
   declare @t1 table( ot_id  int , ot_qty int ,   it_id  int )
   insert into @t1 values(001   ,   40   ,   1 ) 
   insert into @t1 values(002   ,   20   ,   2 )
   
   select a.it_id , min(a.it_qty) -SUM(b.ot_qty) as  qty from @t a
   inner join @t1 b on b.it_id = a.it_id
   group by a.it_id
   having( min(a.it_qty) -SUM(b.ot_qty) >0)