没有基础物品表?那就只能链接取基础物品了。同时当作s1是数量。 select orderTable.order_no,库存=sum(isnull(r.s1,0))-sum(isnull(c.s1,0)) from (select distinct order_no from (select order_no from ruku union select order_no from chuku) a) orderTable left join ruku r on ordertable.order_no=r.order_no left join chuku c on ordertable.order_no=c.order_no group by orderTable.order_no
select orderTable.order_no,s1库存=sum(isnull(r.s1,0))-sum(isnull(c.s1,0)), s2库存=sum(isnull(r.s2,0))-sum(isnull(c.s2,0)), s3库存=sum(isnull(r.s3,0))-sum(isnull(c.s3,0)), s4库存=sum(isnull(r.s4,0))-sum(isnull(c.s4,0)), s5库存=sum(isnull(r.s5,0))-sum(isnull(c.s5,0)) from (select distinct order_no from (select order_no from ruku union select order_no from chuku) a) orderTable left join ruku r on ordertable.order_no=r.order_no left join chuku c on ordertable.order_no=c.order_no group by orderTable.order_no
select a.order_no, sum(a.s1)/case when (select count(1) from orderout where order_no = a.order_no)=0 then 1 else (select count(1) from orderout where order_no = a.order_no) end -sum(b.s1)/case when (select count(1) from orderin where order_no = a.order_no)=0 then 1 else (select count(1) from orderin where order_no = a.order_no) end as s1left,sum(a.s2)/case when (select count(1) from orderout where order_no = a.order_no)=0 then 1 else (select count(1) from orderout where order_no = a.order_no) end -sum(b.s2)/case when (select count(1) from orderin where order_no = a.order_no)=0 then 1 else (select count(1) from orderin where order_no = a.order_no) end as s2left,sum(a.s3)/case when (select count(1) from orderout where order_no = a.order_no)=0 then 1 else (select count(1) from orderout where order_no = a.order_no) end -sum(b.s3)/case when (select count(1) from orderin where order_no = a.order_no)=0 then 1 else (select count(1) from orderin where order_no = a.order_no) end as s3left,
sum(a.s4)/case when (select count(1) from orderout where order_no = a.order_no)=0 then 1 else (select count(1) from orderout where order_no = a.order_no) end -sum(b.s4)/case when (select count(1) from orderin where order_no = a.order_no)=0 then 1 else (select count(1) from orderin where order_no = a.order_no) end as s4left,
sum(a.s5)/case when (select count(1) from orderout where order_no = a.order_no)=0 then 1 else (select count(1) from orderout where order_no = a.order_no) end -sum(b.s5)/case when (select count(1) from orderin where order_no = a.order_no)=0 then 1 else (select count(1) from orderin where order_no = a.order_no) end as s5left from orderin as a left join orderout as b on a.order_no = b.order_no group by a.order_no
哈哈。river提示了我一下了﹐我沒有考慮出庫表中的產品可能在入庫表中沒有(現實可能不存在﹐但是系統可以允許)﹐所以需要修改一下我的語句為﹕ 注﹕orderin 為入庫表﹐orderout 為出庫表,用s1做例子。s2,s3,s4.s5同理﹐則為﹕ select c.order_no, sum(isnull(a.s1,0))/case when (select count(1) from orderout where order_no = c.order_no)=0 then 1 else (select count(1) from orderout where order_no = c.order_no) end -sum(isnull(b.s1,0))/case when (select count(1) from orderin where order_no = c.order_no)=0 then 1 else (select count(1) from orderin where order_no = c.order_no) end as s1left from (select distinct order_no from (select order_no from orderin union select order_no from orderout) a) as c left join orderin as a on c.order_no = a.order_no left join orderout as b on c.order_no = b.order_no group by c.order_no
select orderTable.order_no,库存=sum(isnull(r.s1,0))-sum(isnull(c.s1,0))
from (select distinct order_no
from (select order_no from ruku
union
select order_no from chuku) a) orderTable
left join ruku r on ordertable.order_no=r.order_no
left join chuku c on ordertable.order_no=c.order_no
group by orderTable.order_no
date Datetime,--入\出庫日期
s1 smallInt,--規格庫存量
s2 smallInt,--規格庫存量
s3 smallInt,--規格庫存量
s4 smallInt,--規格庫存量
s5 smallInt--規格庫存量
s2库存=sum(isnull(r.s2,0))-sum(isnull(c.s2,0)),
s3库存=sum(isnull(r.s3,0))-sum(isnull(c.s3,0)),
s4库存=sum(isnull(r.s4,0))-sum(isnull(c.s4,0)),
s5库存=sum(isnull(r.s5,0))-sum(isnull(c.s5,0))
from (select distinct order_no
from (select order_no from ruku
union
select order_no from chuku) a) orderTable
left join ruku r on ordertable.order_no=r.order_no
left join chuku c on ordertable.order_no=c.order_no
group by orderTable.order_no
sum(a.s1)/case when (select count(1) from orderout where order_no = a.order_no)=0
then 1 else (select count(1) from orderout where order_no = a.order_no)
end
-sum(b.s1)/case when (select count(1) from orderin where order_no = a.order_no)=0
then 1 else (select count(1) from orderin where order_no = a.order_no) end as s1left,sum(a.s2)/case when (select count(1) from orderout where order_no = a.order_no)=0
then 1 else (select count(1) from orderout where order_no = a.order_no)
end
-sum(b.s2)/case when (select count(1) from orderin where order_no = a.order_no)=0
then 1 else (select count(1) from orderin where order_no = a.order_no) end as s2left,sum(a.s3)/case when (select count(1) from orderout where order_no = a.order_no)=0
then 1 else (select count(1) from orderout where order_no = a.order_no)
end
-sum(b.s3)/case when (select count(1) from orderin where order_no = a.order_no)=0
then 1 else (select count(1) from orderin where order_no = a.order_no) end as s3left,
sum(a.s4)/case when (select count(1) from orderout where order_no = a.order_no)=0
then 1 else (select count(1) from orderout where order_no = a.order_no)
end
-sum(b.s4)/case when (select count(1) from orderin where order_no = a.order_no)=0
then 1 else (select count(1) from orderin where order_no = a.order_no) end as s4left,
sum(a.s5)/case when (select count(1) from orderout where order_no = a.order_no)=0
then 1 else (select count(1) from orderout where order_no = a.order_no)
end
-sum(b.s5)/case when (select count(1) from orderin where order_no = a.order_no)=0
then 1 else (select count(1) from orderin where order_no = a.order_no) end as s5left
from orderin as a
left join orderout as b
on a.order_no = b.order_no
group by a.order_no
注﹕orderin 為入庫表﹐orderout 為出庫表,用s1做例子。s2,s3,s4.s5同理﹐則為﹕
select c.order_no,
sum(isnull(a.s1,0))/case when (select count(1) from orderout where order_no = c.order_no)=0
then 1 else (select count(1) from orderout where order_no = c.order_no)
end
-sum(isnull(b.s1,0))/case when (select count(1) from orderin where order_no = c.order_no)=0
then 1 else (select count(1) from orderin where order_no = c.order_no) end as s1left
from (select distinct order_no
from (select order_no from orderin
union
select order_no from orderout) a)
as c left join orderin as a on c.order_no = a.order_no
left join orderout as b on c.order_no = b.order_no
group by c.order_no