Visual Foxpro查询挂起数量

问题描述

我有两个表AORDER for Purchase& BORDER出售.我想获取待处理的数量.对于一个采购订单，销售订单可以具有1条以上的记录.我不想显示那些待处理数量为0的订单.我尝试过:

I am having two tables AORDER for Purchase & BORDER for sale. I want to get pending quantity. Sale orders can have more than 1 records against one purchase order. I do not want to show those order having pending quantities 0. I tried this:

SELECT ;
      aorder.orderid,;
      aorder.orderdate,;
      aorder.itemname,;
      aorder.partyname,;
      aorder.qty as Purchase,;
      SUM(border.qty) AS Sale,;
      SUM(aorder.qty-border.qty) as Pending;
   FROM ;
      aorder;
         LEFT JOIN border ;
            ON aorder.orderid = border.porderid;
   GROUP BY ;
      aorder.orderid,;
      aorder.orderdate,;
      aorder.itemname,;
      aorder.partyname,;
      aorder.qty

但是我无法隐藏那些具有购买数量=销售数量的记录.

But I am failed to hide those records having purchase qty = sale qty.

提前Thnx.

推荐答案

正如Shahkalpesh所提到的，您确实需要应用hading，但是您的SUM不正确.

As Shahkalpesh mentioned, you do need to apply the having, but your SUM in incorrect.

应该是

aorder.qty-SUM(border.qty)> 0; &&也供您参考.

aorder.qty - SUM(border.qty) > 0; && also for your field reference.

原因是SUM在求和之内求和每个部分.您将只有一个购买"记录，但有许多销售"记录，就像库存控制先进先出(FIFO)，先进先出(LIFO)等

The reason, SUM is summing each part WITHIN the sum. You will have only one "Purchase" record, but many "Sale" records, as if inventory control First in / First Out (FIFO), Last In / First Out (LIFO), etc

因此，假设您的采购订单#1的数量为10，并且分别出售了数量2、1、1、3、2、1 ...的时间，共6笔销售记录.你在做什么

So, say you have PURCHASE order #1 with a quantity of 10, and have sold separate times for quantities 2, 1, 1, 3, 2, 1... Total of 6 sale records. What you are doing is

sum( 10 - 2 
   + 10 - 1
   + 10 - 1
   + 10 - 3
   + 10 - 2
   + 10 - 1 )

修改后的方法是...

The revised way is...

10 - SUM( 2 + 1 + 1 + 3 + 2 + 1 )

这篇关于Visual Foxpro查询挂起数量的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！