select a.原料号,sum(a.数量), sum(b.数量), sum(a.数量)- sum(b.数量) from 原料库存表 a left join 加工表 b on a.原料号 =b.原料号 group by a.原料号
create table t1 ( code varchar(10), acount int ) insert into t1 select 'B0003', 10 union all select 'B0003', 5 union all select 'B0001', 3 create table t2 ( code varchar(10), acount int ) insert into t2 select 'B0003', 11 union all select 'B0003', 5 union all select 'B0001', 1 union all select 'B0004', 10 select * from t1 select * from t2select code as [原料号],acount1 as [库存数量],acount2 as [加工数量],acount2-acount1 as [短缺数量] from (select a2.code,isnull(a1.acount,0) as acount1,ISNULL(a2.acount,0) as acount2 from (select code,SUM(acount) as acount from t1 group by code) as a1 right join (select code,SUM(acount) as acount from t2 where code>'B0001' group by code) as a2 on a1.code=a2.code) as b1-------------------------- 原料号 库存数量 加工数量 短缺数量 B0003 15 16 1 B0004 0 10 10
-- 测试表 if( object_id('tk') is not null) drop table tk create table tk ( code varchar(10) , qty int ) insert into tk select 'B0003', 10 union all select 'B0003', 5 union all select 'B0001', 3if( object_id('tj') is not null) drop table tj create table tj ( code varchar(10) , qty int ) insert into tj select 'B0003', 11 union all select 'B0003', 5 union all select 'B0001', 1 union all select 'B0004', 10-- 语句 -- 1.根据业务可能只用join就行,无需full join -- 2.使用工式: 短缺数量=加工数量-库存数量, 且加工数量大于库存数量 select code as 原料号, qtyK as 库存数量, qtyJ as 加工数量, (qtyJ-qtyK) as 短缺数量 from (select isnull(a.code, b.code) as code, isnull(a.qty, 0) as qtyK, isnull(b.qty, 0) as qtyJ from (select code, sum(qty) as qty from tk group by code ) as a full join (select code, sum(qty) as qty from tj group by code ) as b on a.code = b.code ) as s where qtyJ > qtyK
group by a.原料号
(
code varchar(10),
acount int
)
insert into t1
select 'B0003', 10 union all
select 'B0003', 5 union all
select 'B0001', 3
create table t2
(
code varchar(10),
acount int
)
insert into t2
select 'B0003', 11 union all
select 'B0003', 5 union all
select 'B0001', 1 union all
select 'B0004', 10
select * from t1
select * from t2select code as [原料号],acount1 as [库存数量],acount2 as [加工数量],acount2-acount1 as [短缺数量] from
(select a2.code,isnull(a1.acount,0) as acount1,ISNULL(a2.acount,0) as acount2 from
(select code,SUM(acount) as acount from t1 group by code) as a1 right join
(select code,SUM(acount) as acount from t2 where code>'B0001' group by code) as a2 on a1.code=a2.code) as b1--------------------------
原料号 库存数量 加工数量 短缺数量
B0003 15 16 1
B0004 0 10 10
-- 测试表
if( object_id('tk') is not null)
drop table tk
create table tk
(
code varchar(10) ,
qty int
)
insert into tk
select 'B0003', 10 union all
select 'B0003', 5 union all
select 'B0001', 3if( object_id('tj') is not null)
drop table tj
create table tj
(
code varchar(10) ,
qty int
)
insert into tj
select 'B0003', 11 union all
select 'B0003', 5 union all
select 'B0001', 1 union all
select 'B0004', 10-- 语句
-- 1.根据业务可能只用join就行,无需full join
-- 2.使用工式: 短缺数量=加工数量-库存数量, 且加工数量大于库存数量
select code as 原料号, qtyK as 库存数量, qtyJ as 加工数量, (qtyJ-qtyK) as 短缺数量
from
(select isnull(a.code, b.code) as code, isnull(a.qty, 0) as qtyK, isnull(b.qty, 0) as qtyJ
from
(select code, sum(qty) as qty
from tk
group by code
) as a
full join
(select code, sum(qty) as qty
from tj
group by code
) as b
on a.code = b.code
) as s
where qtyJ > qtyK