两张有关联的表进行合并
品号 摘要 入库数量 领出数量 领退数量 本月结存
A产品 进货商 100 100
A产品 内部领料 100 0
合计 100 100 0
B产品 进货商1 200 200
B产品 进货商2 150 350
B产品 内部领料 100 50 300
合计 350 100 50 300
。。。。。。。
有进货表一张，有品号，摘要，进货数量，如下表
品号 进货商 进货数量
A产品 进货商 100
B产品 进货商1 50
B产品 进货商1 150
B产品 进货商2 100
B产品 进货商2 50
有领料单一张，有品号，领用部门，领用数量，如下表
品号 领用部门 领用数量
A产品 内部 100
B产品 内部 50
B产品 内部 50
有退料单一张，有品号，退料部门，退还数量，如下表
品号 退料部门 退还数量
B产品 内部 50 SQL
[解决办法]
是这样吗：
;with in_stock
as
(
--查询进货的
select dbo.INVMB.MB002,
dbo.PURMA.MA003,
sum(dbo.PURTH.TH015) as in_stock_count --AS '进货数量'
from INVMB inner JOIN PURTH on INVMB.MB001=PURTH.TH004 and INVMB.MB002=PURTH.TH005
INNER join PURTG on PURTH.TH001=PURTG.TG001 AND PURTH.TH002=PURTG.TG002
inner join PURMA On PURTG.TG005=PURMA.MA001
where PURTH.TH004 LIKE '21%' OR PURTH.TH004 LIKE '22%'
group by INVMB.MB002,PURMA.MA003 with rollup
),
out_stock
as
(
--查询出货的
select INVMB.MB002,
CMSMC.MC002,
SUM(INVTB.TB007) AS out_stock_count --'转出数量'
from INVMB inner JOIN INVTB on INVMB.MB001=INVTB.TB004 and INVMB.MB002=INVTB.TB005
LEFT join INVTA on INVTA.TA001=INVTB.TB001 AND INVTA.TA002=INVTB.TB002
inner join CMSMC On INVTB.TB013=CMSMC.MC001
WHERE INVTA.TA001='1201' AND INVMB.MB001 like '21%' or INVMB.MB001 LIKE '22%'
GROUP BY INVMB.MB002,CMSMC.MC002
)
select t.mb002,
t.mc002,
it.in_stock_count as '进货数量',
ot.out_stock_count as '转出数量'
from
(
select MB002,MC002 from in_stock
union
select MB002,MC002 from out_stock
)t
left join in_stock it
on t.mb002 = it.mb002
left join out_stock ot
on t.mb002 = ot.mb002
;with cte1 as
(
select dbo.INVMB.MB002 AS '品名',
dbo.PURMA.MA003 AS '摘要',
sum(dbo.PURTH.TH015) AS '进货数量'
from INVMB inner JOIN PURTH on INVMB.MB001=PURTH.TH004 and INVMB.MB002=PURTH.TH005
INNER join PURTG on PURTH.TH001=PURTG.TG001 AND PURTH.TH002=PURTG.TG002
inner join PURMA On PURTG.TG005=PURMA.MA001
where PURTH.TH004 LIKE '21%' OR PURTH.TH004 LIKE '22%'
group by INVMB.MB002,PURMA.MA003 with rollup
),
cte2 as
(
select INVMB.MB002 AS '品名',
CMSMC.MC002 AS '摘要',
SUM(INVTB.TB007) AS '转出数量'
from INVMB inner JOIN INVTB on INVMB.MB001=INVTB.TB004 and INVMB.MB002=INVTB.TB005
LEFT join INVTA on INVTA.TA001=INVTB.TB001 AND INVTA.TA002=INVTB.TB002
inner join CMSMC On INVTB.TB013=CMSMC.MC001
WHERE INVTA.TA001='1201' AND INVMB.MB001 like '21%' or INVMB.MB001 LIKE '22%'
GROUP BY INVMB.MB002,CMSMC.MC002
)
select a.品名,a.摘要,SUM(b.进货数量) as 进货数量,SUM(c.转出数量) as 转出数量
,SUM(b.进货数量)-SUM(c.转出数量) as 结余
from
(select 品名,摘要 from cte1
union all select 品名,摘要 from cte2
)a
left join cte1 b on a.品名=b.品名 and a.摘要=b.摘要
left join cte2 c on a.品名=c.品名 and a.摘要=c.摘要
group by a.品名,a.摘要
