用于比较和求和两个 foxpro 表的脚本

Question

我有2张桌子

项目表

ItemCode     BatchNumber   Qty
 Item01      Batch1         50
 Item02      Batch2         75

交易表

ItemCode     BatchNumber   QtyUsed
 Item01      Batch1          4
 Item02      Batch2          7 

期望的输出

ItemCode     BatchNumber   Qty    QtyUsed   Balance
 Item01      Batch1         50       4       46
 Item02      Batch2         75       7       68

什么 sql 脚本来实现此输出？

Answer 1

这应该适合你。 诀窍是预先查询每个集合，以便每个项目/批次组合最多有一条记录。 如果没有，并且您在任一表中都有多个，您将获得笛卡尔结果。 从项目表开始，这些保证有记录，而交易表可能永远不会有所述项目的销售。 这样，首先完成项目预汇总查询，然后与记录比率最多为 1：1 的所有事务的总和进行比较。 但是，如果项目/批次没有相应的事务，NVL（） 将返回零值，但我设置为 000000，因此如果第一条记录具有空值，则仅对单个 0 执行 null 不会得到一位数列宽的答案。

select;
      TmpItem.ItemCode,;
      TmpItem.BatchNumber,;
      TmpItem.SumQ as Qty,;
      cast( NVL( TmpTrans.SumUsed, 0 ) as int) as QtyUsed,;
      TmpItem.SumQ - NVL( TmpTrans.SumUsed, 0 ) as Balance;
   from ;
      ( select ItemCode, BatchNumber, SUM(Qty) as SumQ;
           FROM ItemTable;
           GROUP BY 1, 2 ) TmpItem;
           LEFT JOIN;
           ( select ItemCode, BatchNumber, SUM(QtyUsed) as SumUsed;
                FROM TransTable;
                GROUP BY 1, 2 ) TmpTrans;
           ON TmpItem.ItemCode = TmpTrans.ItemCode;
           AND TmpItem.BatchNumber = TmpTrans.BatchNumber

Answer 2

这是另一种方式。使用 UNION 语句合并两个表。这样，您就可以将所有记录放在一个游标/表中。在第二个语句中，您可以GROUP BY获取Qty、QtyUsed和Balance字段的总计。

有时，将工作拆分为较小的选择语句可以使问题更容易弄清楚。

SELECT i.ItemCode, i.BatchNumber, i.Qty, 0000 as QtyUsed ;
    FROM Item i ;
UNION ;
SELECT t.ItemCode, t.BatchNumber, 0000 as Qty, t.QtyUsed ;
    FROM Transaction t ;
    INTO CURSOR one
SELECT ItemCode, BatchNumber, SUM(Qty) as Qty, SUM(QtyUsed) as QtyUsed, (SUM(Qty) - SUM(QtyUsed)) as Balance ;
    FROM one ;
    INTO CURSOR two ;
    GROUP BY ItemCode, BatchNumber

Answer 3

简单地说：

SELECT a.ItemCode, a.BatchNumber, a.Qty
       b.QtyUsed, a.Qty - b.QtyUsed as Balance
  FROM ItemTable a
  LEFT JOIN TransactionTable b ON a.ItemCode = b.ItemCode 
                          and a.BatchNumber = b.BatchNumber

确定项目代码和批号是否不同