我创建了一个 SQLFiddle (谢谢，Amadan!)我的模式和一些测试数据。由于某种原因，计算列在 SQLFiddle 中不起作用，但它们在我的 MYSQL Workbench 中运行良好。假设它们正常工作。

我的查询已经走到这一步了:

SELECT DISTINCT o.orderid,i.invoiceid,i.subinvoicenumber,stockingorderid AS oistockingorderid,

IF(stockingorderid > 0, 0, po.purchaseorderid) AS purchaseorderid,subponumber,po.paidvia,dropshipfee,i.taxstate,

COALESCE(
    (SELECT mfrname 
        FROM cs_products.tblstockingorders so 
        WHERE so.stockingorderid=oistockingorderid),
    po.mfrname)
AS source,

(i.shipping + i.surcharge + 
    (SELECT SUM(additionalshipping * quantity) AS additionalshipping 
        FROM cs_products.tblorderitems 
        WHERE invoiceid=i.invoiceid)) 
AS shipping,

CAST(IF(o.paymentmethod=2, CONCAT('Check: ', i.checknumber),o.paymentmethod) AS CHAR) AS invoicepaidvia,

COALESCE(
    (SELECT (SUM(cost * quantity) * IF(so.mfrdiscount > 0, 1 - so.mfrdiscount, 1)) AS cost 
        FROM cs_products.tblorderitems oi 
        JOIN cs_products.tblallocations a ON oi.orderitemid=a.orderitemid 
        JOIN cs_products.tblstockingorders so ON a.stockingorderid=so.stockingorderid
        WHERE a.stockingorderid=oistockingorderid),
    (SELECT (SUM(cost * quantity) * IF(po.mfrdiscount > 0, 1 - po.mfrdiscount, 1)) AS cost 
        FROM cs_products.tblorderitems 
        WHERE purchaseorderid=po.purchaseorderid)) 
    AS grosscost,

(SELECT SUM(price * quantity)
    FROM cs_products.tblorderitems 
    WHERE invoiceid=i.invoiceid) 
AS grossprice

FROM cs_products.tblorders o 
    JOIN cs_products.tblinvoices i ON o.orderid=i.orderid
    #ordertype of 0 means the order came from the website
    LEFT JOIN cs_products.tblpurchaseorders po ON o.orderid=po.orderid AND IF(o.ordertype<>0, subinvoicenumber=subponumber, subinvoicenumber=0 AND subponumber>=0)
    LEFT JOIN cs_products.tblallocations a ON a.orderid=o.orderid

这让我完成了 95% 的事情。如您所见，对于 OrderId 1287，它显示了两 strip 有 stockingorderid 的记录，但没有一 strip 有 purchaseorderid，即使采购订单已归因到 1287。对于丢失的行，我期望看到的是这样的:

1287|276|0|NULL|194|0||0|0|'Quality Fabricators'|357.53|0|781.43|11917.70

同样，不用担心计算列，我只希望记录出现。我觉得我错过了最小的东西，但我不知道它是什么。

谢谢!

更新 计算列不起作用的原因是因为使用了旧的订单项目数据。我会在有机会时更新它。

最佳答案

我建议您使用 NULL(而不是 0)来表示 stockingorderid 和 purchaseorderid PO 和 SO 记录分别为:

ALTER TABLE tblorderitems MODIFY purchaseorderid int(10) unsigned NULL;
UPDATE tblorderitems SET purchaseorderid = NULL WHERE purchaseorderid = 0;

然后，您可以使用 UNION 子查询加入您的查询，该子查询提供一组所有 (order, stockingorder) 和 (订单，采购订单)对:

  SELECT orderid, stockingorderid, NULL AS purchaseorderid FROM tblallocations
UNION ALL
  SELECT orderid, NULL AS stockingorderid, purchaseorderid FROM tblpurchaseorders

我认为您的查询可以简化为:

SELECT   orderid, invoiceid, subinvoicenumber, purchaseorderid, subponumber,
         tblpurchaseorders.paidvia, dropshipfee, tblinvoices.taxstate,
         invoicedate, datepaid, stockingorderid,

         COALESCE(tblstockingorders.mfrname, tblpurchaseorders.mfrname) AS source,

         shipping + surcharge + SUM(quantity * additionalshipping) AS shipping,

         CASE paymentmethod
           WHEN 2 THEN CONCAT('Check: ', checknumber)
           ELSE CAST(paymentmethod AS CHAR)
         END AS InvoicePaidVia,

         (1-COALESCE(tblstockingorders.mfrdiscount, tblpurchaseorders.mfrdiscount))
           * SUM(quantity * cost) AS grosscost,

         SUM(quantity * price) AS grossprice

FROM     tblorders
  JOIN   tblinvoices          USING (orderid)
  JOIN (
          SELECT orderid, orderitemid, stockingorderid, NULL AS purchaseorderid
          FROM   tblallocations
        UNION ALL
          SELECT orderid, NULL, NULL, purchaseorderid
          FROM   tblpurchaseorders
       ) AS t                 USING (orderid)
  LEFT JOIN tblpurchaseorders USING (orderid,purchaseorderid)
  LEFT JOIN tblstockingorders USING (stockingorderid)
  LEFT JOIN tblorderitems     USING (orderid,orderitemid,invoiceid,purchaseorderid)
WHERE    ordertype = 0 OR subinvoicenumber = subponumber
GROUP BY orderid, invoiceid, subinvoicenumber, purchaseorderid, subponumber,
         tblpurchaseorders.paidvia, dropshipfee, tblinvoices.taxstate, datepaid,
         invoicedate, stockingorderid, shipping, surcharge, paymentmethod,
         tblstockingorders.mfrname, tblpurchaseorders.mfrname, checknumber,
         tblstockingorders.mfrdiscount, tblpurchaseorders.mfrdiscount

查看 sqlfiddle ，删除了对 invoicedate 列的引用(因为它不存在于您的示例架构中)。

关于mysql - 复杂的 MySQL 查询，我离它很近，我可以闻到它，我们在Stack Overflow上找到一个类似的问题： https://stackoverflow.com/questions/10695591/