A表(明细)
ID号 编码 客户 办类 入仓数 出仓数 退料数 当时库存 盘点更改量
id code cust smpltype in out back mxstore inventory
1 c001 A 大办 100 0 0 100 0
3 c001 B 初办 100 0 0 100 0
6 c001 A 大办 0 50 50 0
4 c001 A 初办 30 0 0 30 0
5 c001 A 初办 0 0 0 0 -10
2 c002 A 初办 50 0 0 50 0
7 c002 A 初办 0 20 0 30 0
8 c002 A 初办 0 0 10 40 0
B表(库存)
编码 客户 办类 库存数
code cust smpltype sjstore
c001 A 大办 50
c001 A 初办 20
c001 B 初办 100
c002 A 初办 40
我想得到的结果:
编码 客户 办类 明细库存=(sum(in)-sum(out)+sum(back)+sum(inventory)) 实际库存
code cust smpltype num sjstore
c001 A 大办 50 50
c001 B 初办 100 100
c001 A 初办 20 20
c002 A 初办 40 40
ID号 编码 客户 办类 入仓数 出仓数 退料数 当时库存 盘点更改量
id code cust smpltype in out back mxstore inventory
1 c001 A 大办 100 0 0 100 0
3 c001 B 初办 100 0 0 100 0
6 c001 A 大办 0 50 50 0
4 c001 A 初办 30 0 0 30 0
5 c001 A 初办 0 0 0 0 -10
2 c002 A 初办 50 0 0 50 0
7 c002 A 初办 0 20 0 30 0
8 c002 A 初办 0 0 10 40 0
B表(库存)
编码 客户 办类 库存数
code cust smpltype sjstore
c001 A 大办 50
c001 A 初办 20
c001 B 初办 100
c002 A 初办 40
我想得到的结果:
编码 客户 办类 明细库存=(sum(in)-sum(out)+sum(back)+sum(inventory)) 实际库存
code cust smpltype num sjstore
c001 A 大办 50 50
c001 B 初办 100 100
c001 A 初办 20 20
c002 A 初办 40 40
select A.code,A.cust,A.smpltype,sum(A.in)-sum(A.out)+sum(A.back)+sum(A.inventory),B.sjstore
from A left join B on (A.code=B.code and A.smpltype=B.smpltype and A.cust=B.cust)
group by code,cust,smpltype
你这条数据有问题吧?试下这个:
select a.code,a.cust,a.smpltype,ifnull(sum(b.`in`-b.`out`+b.back+b.inventory),0) as num,a.sjstore
from B表 a left join A表 b
on a.code=b.code and a.cust=b.cust and a.smpltype=b.smpltype
group by a.code,a.cust,a.smpltype
order by a.code,a.cust,a.smpltype注意,用左连接,目的是为了防止库存表里有的记录而在明细表里没对应记录的情况出现。