select a.a1 as sName, a.a3 as sDate, a.aa2 - b.bb2 as nLeft
from
(
select a1, sum(a2) as aa2, a3 from input group by a1,a3
) a,
(
select b1, sum(b2) as bb2, b3 from output group by b1,b3
) b不知大家看看有没有隐患?
from
(
select a1, sum(a2) as aa2, a3 from input group by a1,a3
) a,
(
select b1, sum(b2) as bb2, b3 from output group by b1,b3
) b不知大家看看有没有隐患?
select input.a1,input1.a22 - output1.b22
.
.
group by input.a1
是典型的分组统计结构
input1.a22 - output1.b22要写成SUM(input1.a22 - output1.b22)
这样看看行不行不能直接在select中使用运算,加一个as才可以把
select i1.a1,i1.a2 - o1.b2
from (select a1,sum(a2)as a2
from input where input.a3 <'20020801' group by a1) i1
left join
( select b1,b2
from output where b3 <'20020801' group by b1) o1 where input1.a11=output1.b11
如果有null值在关联时判断一下
from (select a1,a2 from input where a3<'20020801'
union all
select b1,-1*b2 from output where b3<'20020801')
group by a1
select a1,sum(a2) a22 from input where a3< '20020801' group by a1
得到如下结果
产品名称 数量
--- ---
--- ---
.....
2、出库产品、数量列表
select b1,sum(b2) b22 from ouput where b3< '20020801' group by a1
得到如下结果
产品名称 数量
--- ---
--- ---
.....
3、把出入库数量放在一起
select A.a1,A.a22,B.b22 from
(
(select a1,sum(a2) a22 from input where a3< '20020801' group by a1)A
left join
(select b1,sum(b2) b22 from ouput where b3< '20020801' group by b1)B
) on A.a1=B.b1得到如下结果:
产品名称 入库数量 出库数量
---- --- ---
---- --- ---
---- --- ---
.....4、期初库存
select a1,a22-b22 from
(select A.a1,A.a22,B.b22 from
(
(select a1,sum(a2) a22 from input where a3< '20020801' group by a1)A
left join
(select b1,sum(b2) b22 from ouput where b3< '20020801' group by b1)B
) on A.a1=B.b1)楼上bobfang(匆匆过客) 的方法是对的,而且更简单,效率更高!
A1 name
A2 amount
A3 date
出库
B1 name
B2 amount
B3 date假定入库时间20020801,出库时间20020831select input1.a11,input1.a22-output1.b22
from (select a1 as a11 ,sum(a2) as a22
from input
where input.a3<'20020801'
group by a1) input1 ,
(select b1 as b11,sum(b2) as b22
from output
where output.b3<'20020801'
group by b1) output1
where input1.a11=output1.b11期初结存:
select input1.a11 as PM,input1.a22-output1.b22 as NowLeft
from (select a1 as a11 ,sum(a2) as a22
from input
where input.a3<'20020801'
group by a1) input1 ,
(select b1 as b11,sum(b2) as b22
from output
where output.b3<'20020801'
group by b1) output1
where input1.a11=output1.b11本期入库:
select *
from input
where input.a3>'20020801' and input.a3<'20020831'本期出库:
select *
from output
where output.b3>'20020801' and output.b3<'20020831'
from (select a.a1,a.a2 from input a where a.a3<'20020801'
union all
select b.a1,0-b.a2 from output b where b.a3<'20020801')
group by a1