小计和总列

Question

在Visual Foxpro 9中，我正在尝试使用产品“小计”列和报表“总计”列编写一个sql。
工作的sql代码如下所示，但是当我插入注释掉的“Case”代码时，我得到的错误似乎随着我纠正上一个错误而增加。
任何人都可以告诉我应该在哪个地方插入“case”，代码出了什么问题？

SELECT qItemSaleLines.ItemID, ; 
qItems.ItemID, ; 
qItemSaleLines.SaleID, ; 
qSales.SaleID, ; 
qSales.CardRecordID, ; 
qCustomers.CardRecordID, ; 
qItems.ItemNumber AS ProdCODE, ; 
qItems.ItemName AS StkNAME, ; 
qCustomers.LastName AS CUSTOMER, ; 
qSales.InvoiceNumber AS SaleINVNo, ; 
qSales.InvoiceDate AS SaleDATE, ; 
qItemSaleLines.Quantity AS SaleQTY, ; 
qItemSaleLines.TaxExclusiveTotal AS SALE, ; 
qItemSaleLines.CostOfGoodsSoldAmount AS COGS, ; 
qItemSaleLines.TaxExclusiveTotal - qItemSaleLines.CostOfGoodsSoldAmount AS MARGIN, ; 
(qItemSaleLines.TaxExclusiveTotal - qItemSaleLines.CostOfGoodsSoldAmount) */  qItemSaleLines.TaxExclusiveTotal AS MPERCENT ; 
FROM qItemSaleLines, qItems, qSales, qCustomers ; 
WHERE qSales.CardRecordID = qCustomers.CardRecordID AND qItemSaleLines.SaleID = qSales.SaleID AND ; 
qItemSaleLines.ItemID = qItems.ItemID AND qSales.InvoiceDate > {^2009-06-30} ; 
ORDER BY qItems.ItemNumber, qSales.InvoiceDate ; 


*!* (SELECT qItems.ItemID, qItemSaleLines.ItemID, qItemSaleLines.TaxExclusiveTotal, ; 
*!*   CASE WHEN qItems.ItemID = (SELECT TOP 1 qItems.ItemID FROM qItems.ItemID, ; 
*!*    WHERE qItems.ItemID = qItemSaleLines.ItemID, ; 
*!*    ORDER BY qItems.ItemID desc), ; 
*!*   THEN (SELECT SUM(qItemSaleLines.TaxExclusiveTotal) FROM qItemSaleLines.TaxExclusiveTotal,; 
*!*    WHERE qItems.ItemID <= qItemSaleLines.ItemID AND qItems.ItemID = qItemSaleLines.ItemID, ; 
*!*   ELSE ' ' END AS 'PROD-SALE'), ; 
*!*  CASE WHEN qItems.ItemID = (SELECT TOP 1 qItems.ItemID FROM qItems.ItemID, ; 
*!*    ORDER BY qItems.ItemID desc), ; 
*!*  THEN (SELECT SUM(qItemSaleLines.TaxExclusiveTotal) FROM qItemSaleLines.TaxExclusiveTotal, ; 
*!*   ELSE ' ' END AS 'Grand Total') ; 

Answer 1

此外，如果你只是想总计为表中的列，你可以不喜欢

select ; 
     Tbl.YourColumns,; 
     PerItem.TotalPerItem,; 
     RptTotal.TotalPerAll; 
    from ; 
     YourOtherTables Tbl,; 
     (select YourSalesTable.ItemID,; 
       sum(CalculatedSales) as TotalPerItem; 
      From; 
       YourSalesTable; 
      Group by ; 
       ItemID) PerItem,; 
     (select sum(CalculatedSales) as TotalPerAll; 
      From ; 
       YourTalesTable) RptTotal; 
    where ; 
     YourOtherJoinConditions; 
     AND YourOtherTables.ItemID = PerItem.ItemID; 
    order by ; 
     whatever; 
    into ; 
     cursor YourReportResults 

通过由ITEMID做一个SQL-选择为你的最后2台（一个分组）将创建每个项目的总数。通过使最终的连接只有别名PerItem的ItemID，您将获得总共的列数。但是，由于没有加入别名的RptTotal，所以您将得到一个笛卡尔连接...但由于其始终为1条记录，因此每行将具有与其“TotalPerAll”列相同的值。

我希望这两种解决方案能满足您的需求。

Answer 2

首先，VFP不支持在字段级别构建一个case-case。此外，你的子选择在外地后，已经从逗号表和地方之前，和where子句，订单之前...如

select * from MyTable, where SomeCondition, Order by ... 

如果做一个VFP报告，你不后您不必手动将行添加到您的原始数据中，这应该通过数据分组在报告中完成，并添加您的ItemID作为组的基础。然后，将每个项目的“可汇总”列复制/粘贴到报告的组页脚区域。双击该字段，并进行计算，将其告知sum（），并在每个组的末尾重置（即：ItemID）。然后，包括一个报告汇总带。这将为整个报表打印一次...与项目级别的汇总项目一样，再次复制/粘贴，但放入报表汇总区域。双击这些元素进行求和（）并在报告结束时重置。但是，由于您在VFP9中运行，并且您可能希望使用已包含在其各自职位中的行来转出数据，因此我会分解为单独的查询并将结果合并到一起，如我在下面采样的那样。在没有任何必要的聚合的情况下预先提取报告数据的行项目支持。

SELECT ; 
     qItemSaleLines.ItemID, ; 
     qItemSaleLines.SaleID, ; 
     qCustomers.CardRecordID, ; 
     qItems.ItemNumber AS ProdCODE, ; 
     qItems.ItemName AS StkNAME, ; 
     qCustomers.LastName AS CUSTOMER, ; 
     qSales.InvoiceNumber AS SaleINVNo, ; 
     qSales.InvoiceDate AS SaleDATE, ; 
     qItemSaleLines.Quantity AS SaleQTY, ; 
     qItemSaleLines.TaxExclusiveTotal AS SALE, ; 
     qItemSaleLines.CostOfGoodsSoldAmount AS COGS, ; 
     qItemSaleLines.TaxExclusiveTotal - qItemSaleLines.CostOfGoodsSoldAmount AS MARGIN, ; 
     (qItemSaleLines.TaxExclusiveTotal - qItemSaleLines.CostOfGoodsSoldAmount)/qItemSaleLines.TaxExclusiveTotal AS MPERCENT, ; 
     "1" as TierLevel,; 
     "1" as SubTier,; 
     SPACE(50) as GroupCaption; 
    FROM ; 
     qSales, ; 
     qCustomers, ; 
     qItemSaleLines, ; 
     qItems, ; 
    WHERE ; 
      qSales.CardRecordID = qCustomers.CardRecordID ; 
     AND qSales.SaleID = qItemSaleLines.SaleID ; 
     AND qItemSaleLines.ItemID = qItems.ItemID ; 
     AND qSales.InvoiceDate > {^2009-06-30} ; 
    ORDER BY ; 
     qItems.ItemNumber, ; 
     qSales.InvoiceDate ; 
    INTO ; 
     CURSOR C_TmpAllLineItemResults READWRITE 

*/ NOW, get your individual "ITEM GROUP" totals from ABOVE results... 
SELECT ; 
     TR.ItemID, ; 
     TR.ItemNumber AS ProdCODE, ; 
     TR.ItemName AS StkNAME, ; 
     "1" as TierLevel,; 
     "2" as SubTier,; 
     "Subtotal by " + TR.ItemNumber as GroupCaption; 
     SUM(TR.SaleQty) as SaleQty,; 
     SUM(TR.Sale) as Sale,; 
    FROM ; 
     C_TmpAllLineItemResults TR; 
    GROUP BY ; 
     1, 2, 3, 4, 5, 6; 
    ORDER BY ; 
     1; 
    INTO ; 
     CURSOR C_SubTotalPerItem READWRITE 


*/ NOW, get your REPORT totals from ABOVE results... 
SELECT ; 
     "2" as TierLevel,; 
     "Report Totals " as GroupCaption; 
     SUM(STBI.SaleQty) as SaleQty,; 
     SUM(STBI.Sale) as Sale,; 
    FROM ; 
     C_SubTotalPerItem STBI; 
    GROUP BY ; 
     1, 2; 
    INTO ; 
     CURSOR C_ReportTotals READWRITE 

*/ Finally, merge them together... since the append from will add records with matching columns, 
*/ even though a subtotal cursor doesn't have all the same columns, VFP doesn't care, just 
*/ adds for columns that DO match the table its going into. 
SELECT C_TmpAllLineItemResults 
APPEND FROM DBF("C_SubTotalPerItem") 
APPEND FROM DBF("C_ReportTotals") 

*/ Now, they are all merged... Finally, build an index for your report 
INDEX on TierLevel + ItemID + SubTier TAG RptOrder 

*/ Now, browse/review the order and adjust as needed...