求一SQL语句

[lzh_911]

现有两个表如下:

送货表a
out_id, out_no, product_no, out_sl
说明:out_id是自动增ID,product_no是工单号,out_sl是送货数量,out_no送货单号
开发票表b
id , out_no, product_no, sl
说明:id是自动增ID,product_no是工单号,sl是已开发票数量,out_no送货单号
要求实现功能:
a表中未开发票的数量?

[lzh_911]

如果B表中没有记录

[LouisXIV]

--那么就用这个好了

select a.out_no,a.product_no, a.out_sl ,b.sl,a.out_sl - isnull(b.sl,0) as aa
from a left outer join b
on a.product_no=b.product_no and a.out_no=b.out_no

[冷箫轻笛]

没太明白

a表中未开发票的数量 = a.out_sl - b.sl

??

[zcyan666]

select a.out_no,a.product_no, a.out_sl ,b.sl,a.out_sl - b.sl as aa
from a,b
where a.product_no=b.product_no and a.out_no=b.out_no

[wk_1978]

if object_id('a') is not null
drop table a
if object_id('b') is not null
drop table b

create table a(out_id numeric(10) Identity(1,1), out_no varchar(30) , product_no varchar(30), out_sl numeric(12,2))
create table b([id] numeric(10) Identity(1,1), out_no varchar(30), product_no varchar(30), sl numeric(12,2))
insert into a(out_no,product_no,out_sl)
select '01','01',10
union all select '02','01',15
union all select '03','02',20

insert into b(out_no,product_no,sl)
select '01','01',10
union all select '02','01',10
union all select '03','02',12

Select t1.out_no,t1.product_no,(isnull(t1.out_sl,0) - isnull(t2.sl,0)) as 未开票数量
from
(select a.out_no,a.product_no,sum(out_sl) as out_sl
from a
group by a.out_no,a.product_no) t1
left join
(Select b.out_no,b.product_no,sum(sl) as sl from b group by b.out_no,b.product_no) t2
on (t1.out_no = t2.out_no) and (t1.product_no = t2.product_no)


if object_id('a') is not null
drop table a
if object_id('b') is not null
drop table b