需要减去无法引用的字段的Mysql查询

Question

我们的数据库将客户订单存储在两个表中:customerorders 和 customerorderlines。在其他字段中，有一个 customerorders.type 字段决定(= 1 或 = 2)如果该客户订单是发票或贷方票据。

我们目前有一份报告，其中列出了已售单位数量、利润等，但通过当前查询，仅显示该期间内已售单位总数，即，它不会减去积分数量(如果有)。下面是它的 sql 以及它生成的结果的示例:

(对接下来的大量查询表示歉意:))

SELECT l.name AS locationname
, sr.name AS salesrepname
, ct.name AS customertypename
, c.name AS customername
, c.id AS customer_id
, c.code
, s.name AS suppliername
, p.description AS productname
, p.id AS product_id
, p.unitofmeasure
, SUM(col.vatableprice) AS totalsales
, SUM(col.vatprice) AS vat
, SUM(col.quantity) AS totalitems
, SUM(col.quantity * col.costprice) AS totalsalecost
, SUM(col.vatableprice) - SUM(col.quantity * col.costprice) AS totalprofit
,(
     SELECT SUM(col2.vatableprice) AS totalsales 
     FROM customerorders AS co2 
     LEFT JOIN customerorderlines AS col2 ON col2.customerorder_id = co2.id
     LEFT JOIN customers AS c2 ON c2.id = co2.customer_id
     LEFT JOIN locations AS l2 ON l2.id = c2.location_id
     LEFT JOIN customertypes AS ct2 ON ct2.id = c2.customertype_id
     LEFT JOIN salesreps AS sr2 ON sr2.id = c2.salesrep_id
     LEFT JOIN products AS p2 ON p2.id = col2.product_id
     LEFT JOIN suppliers AS s2 ON s2.id = p2.supplier_id
     WHERE c.salesrep_id = c2.salesrep_id
     AND co2.type = 2 AND p2.supplier_id = 179
     AND co2.orderdate >= '2010-01-01 00:00:00'
     AND co2.orderdate <= '2010-02-01 23:59:59'
) AS credits 
,(
    SELECT SUM(col2.vatprice) AS totalvat FROM customerorders AS co2
    LEFT JOIN customerorderlines AS col2 ON col2.customerorder_id = co2.id
    LEFT JOIN customers AS c2 ON c2.id = co2.customer_id
    LEFT JOIN locations AS l2 ON l2.id = c2.location_id
    LEFT JOIN customertypes AS ct2 ON ct2.id = c2.customertype_id
    LEFT JOIN salesreps AS sr2 ON sr2.id = c2.salesrep_id
    LEFT JOIN products AS p2 ON p2.id = col2.product_id
    LEFT JOIN suppliers AS s2 ON s2.id = p2.supplier_id
    WHERE c.salesrep_id = c2.salesrep_id
    AND co2.type = 2
    AND p2.supplier_id = 179
    AND co2.orderdate >= '2010-01-01 00:00:00'
    AND co2.orderdate <= '2010-02-01 23:59:59'
) AS creditsvat

FROM customerorders AS co
LEFT JOIN customerorderlines AS col ON col.customerorder_id = co.id
LEFT JOIN customers AS c ON c.id = co.customer_id
LEFT JOIN locations AS l ON l.id = c.location_id
LEFT JOIN customertypes AS ct ON ct.id = c.customertype_id
LEFT JOIN salesreps AS sr ON sr.id = c.salesrep_id
LEFT JOIN products AS p ON p.id = col.product_id
LEFT JOIN suppliers AS s ON s.id = p.supplier_id
WHERE co.status_v = 5 
AND co.type = 1
AND p.supplier_id = 179
AND co.orderdate >= '2010-01-01 00:00:00' AND co.orderdate <= '2010-02-01 23:59:59'
GROUP BY c.salesrep_id

在这种情况下(分组等由应用程序代码确定)会生成“每个销售代表”报告:

Rep | TotalItems | SalesValue| CostOfSales | Profit   | VAT      | Credits | Credits(VAT)
Rep1| 937        | £5796.49  | £3606.49    | £2190.00 | £1013.73 | £220.12 | £38.57
Rep2| 1905       | £11695.09 | £7314.95    | £4380.14 | £2045.32 | £268.85 | £47.00
Rep3| 1074       | £6346.61  | £3950.53    | £2396.08 | £1109.76 | £54.89  | £9.57
Rep4| 2687       | £16129.42 | £10171.65   | £5957.77 | £2820.46 | £839.15 | £146.78

因此，问题在于 TotalItems 是已售商品的绝对数量(所有类型 = 1 的 customerorders)。积分字段显示类型 = 2(即已退回)期间的项目总成本。 TotalItems 应该从中扣除信用数量，以便一目了然地可以看到实际售出的商品，当然所有其他字段也需要相互扣除相应的信用数量，以便反射(reflect)所售商品的正确金额。

起初我认为这只是对现有查询的简单修改，但后来注意到我无法在选择中引用子查询别名，因此我使用 JOIN (SELECT ....) AS sales/JOIN 重写了整个查询(SELECT .....)AS 积分，这样我就可以从查询顶部的 SELECT 引用 sales.qty 和 Credits.qty，但除非您执行少量查询，否则根本无法扩展。

这就是我走了多远:

(是的，我在这里查询不同的东西......这本质上是最简单的查询形式:单个产品的销售/信用)

SELECT sr.name AS salesrepsname
,l.name AS locationname
,sup.name AS suppliername
,p.description AS productname
,sales.qty AS sold
,credits.qty AS credits
,sales.qty - credits.qty AS actualsold
FROM
customerorders co
LEFT JOIN customerorderlines col ON col.customerorder_id = co.id
LEFT JOIN customers c ON c.id = co.customer_id
LEFT JOIN products p ON p.id = col.product_id
LEFT JOIN salesreps sr ON sr.id = c.salesrep_id
LEFT JOIN locations l ON l.id = c.location_id
LEFT JOIN suppliers sup ON sup.id = p.supplier_id 
JOIN (SELECT SUM(col.quantity) AS qty,
SUM(col.vatableprice) AS total FROM customerorderlines col
LEFT JOIN customerorders co ON co.id = col.customerorder_id
WHERE col.product_id = 27642 AND co.type = 1)
AS sales
JOIN (SELECT SUM(col2.quantity) AS qty FROM customerorderlines col2
LEFT JOIN customerorders co2 ON co2.id = col2.customerorder_id
WHERE col2.product_id = 27642 AND co2.type = 2) AS credits
WHERE col.product_id = 27642
GROUP BY c.salesrep_id

所以我不得不承认我有点卡住了，根本不了解 mysql。

非常欢迎任何建议，请随时向我指出我应该阅读的有关高级子查询和联接的任何文献。

干杯!

Answer 1

我认为您可能正在寻找的技巧就是沿着这些思路。在您想要修复的任何给定查询/子查询中，使用像这样的 SQL，选择所有行，无论它是发票还是贷方票据，并根据订单/资金的方式对正值或负值求和去:

SELECT 
  SUM(CASE WHEN co.type = 1 THEN col.quantity ELSE -col.quantity END)
FROM
  ...

明白了吗？您收回所有订单行，但是当您进行求和时，您将正数贷方和负数借方相加，以便在一次操作中获得实际总数。