34,875
社区成员
发帖
与我相关
我的任务
分享如何统计入库出库数量，合计
我的数据库设计如下
Orders:OrderNO,IsInput,...
Details:KeyNo,OrderNo,ProduceID,Quantity,Unit,price,Total
(其中Isput为入出库标志位，为入库单为1，否则0)
我想统计ProduceID,的入口数量，入口金额合计，出库数量，出口金额合计
Orders:OrderNO,IsInput,...
Details:KeyNo,OrderNo,ProduceID,Quantity,Unit,price,Total
(其中Isput为入出库标志位，为入库单为1，否则0)
我想统计ProduceID,的入口数量，入口金额合计，出库数量，出口金额合计
...全文
请发表友善的回复…
发表回复
lysoon 2003-09-25
- 打赏
- 举报
OK
hdslah 2003-09-24
- 打赏
- 举报
select ProduceID,sum(Quantity),sum(Unit),sum(price),sum(Total) from details a inner join orders b on a.orderno=b.orderNo where b.isinput=1 group by ProduceID
txlicenhe 2003-09-24
- 打赏
- 举报
Select a.ProduceID,
sum(case when b.IsInput = 1 then a.Quantity else 0 end) as 入库数量，
sum(case when b.IsInput = 1 then a.Quantity * a.price else 0 end) as 入库金额，
sum(case when b.IsInput = 0 then a.Quantity else 0 end) as 出库数量，
sum(case when b.IsInput = 0 then a.Quantity * a.price else 0 end) as 出库金额
from Details a
join Orders b on a.OrderNo = b.OrderNo
group by a.ProduceID
sum(case when b.IsInput = 1 then a.Quantity else 0 end) as 入库数量，
sum(case when b.IsInput = 1 then a.Quantity * a.price else 0 end) as 入库金额，
sum(case when b.IsInput = 0 then a.Quantity else 0 end) as 出库数量，
sum(case when b.IsInput = 0 then a.Quantity * a.price else 0 end) as 出库金额
from Details a
join Orders b on a.OrderNo = b.OrderNo
group by a.ProduceID
