sql 计算 查询

来源：百度知道 编辑：UC知道 时间：2024/06/17 18:42:36
主表是订单表
dingdan

danhao chanping shuliang
A001 A 500
A001 B 400
A001 C 500
B002 A 200
B002 B 1500

......相同的单号下有不同的产品和数量

另一个表是进度表

jindu

riqi chanping shuliang danhou
2009-1-1 A 30 A001
2009-2-5 A 60 A001
2009-1-15 B 60 A001
2009-1-1 B 60 A002
2009-1-1 B 150 A002

查询结果

自定义 按单号查询 如:A001

danhao chanping shuliang wancheng chazhi
A001 A 500 90 410
A001 B 400 60 340
A001 C 500 0 500
chazhi 就是差值 汇总以后还差的数 是派生出来的字段 表里没有

select a.danhao,a.chanpin,a.shuliang,b.wancheng,a.shuliang-b.wancheng chazhi from dingdan A,(select danhao,sum(shuliang) wancheng from jindu group by danhao) b
and a.danhao='A001'
and a.danhao=b.danhao

--用left join 和isnull处理
select
a.*,isnull(b.shuliang,0) as wancheng,a.shuliang-isnull(b.shuliang,0) as chazhi
from dingdan a
left join (select chanping,danhou,sum(shuliang) as shuliang from jindu group by chanping,danhou)b on a.danhao=b.danhou and a.chanping=b.chanping
where a.danhao='A001'

chazhi 是哪里来的

没明白。你要做什么。、

说中文