mysql - 在多行上使用 LEFT JOIN 简化我的 SQL

Question

我现在对 MySQL 和高效查询有相当的经验，但仍然遇到问题......

我有两个表，“invoices”和“salesRecords”，Invoices 表包含发票的基本概览，而 Sales Records 逐行分割有关购买项目的特定信息。

为简单起见，我们假设表格如下所示:

发票

id    |   repairCharge     |    shippingCharge      |     total
 1    |       28.95        |         0              |     30.45   
 2    |       10.00        |         8.50           |     29.50

//注意;总计列包括销售记录中显示的项目

销售记录

invNo  |    itemNo   |  price    |   quantity   | discount  |   
  1    |     123     |  1.50     |      1       |     0     |  
  2    |     121     |  6.50     |      1       |     1.5   |  
  2    |     128     |  5.50     |      1       |     0     |  

我想获取一个日期段内售出商品的总值(value)、人工费和运费，所以我需要:

1. 汇总 Invoices 表中的所有行
2. JOIN(左？)InvNo 和 SUM((价格折扣)*数量)上的 SalesRecords 表
3. 为日期期间使用 WHERE 子句

我立即写了这条S​​QL:

SELECT SUM((sr.price-sr.discount)*sr.quantity) as income,   
   SUM(i.repairCharge) as labour,
   SUM(i.shippingCharge) as carriage 
FROM invoices i 
    LEFT JOIN salesRecords sr ON sr.invNo=i.id
WHERE i.dateTime BETWEEN 1431647999 AND 1434360348`

这是错误的

因为，对于具有多个项目行的任何发票，我得到两个表的多个实例，因此 repairCharge 和 shippingCharge 值加倍或增加三倍等，具体取决于该发票上购买了多少产品线。

所以，我四处乱逛并想出了一个解决方案......但它非常丑陋并且可能效率低下:

SELECT SUM(income) as income,
       SUM(labour) as labour,
       SUM(carriage) as carriage 
FROM (SELECT (SELECT SUM((price-discount)*quantity)
              FROM salesRecords
              where invno=i.id
              GROUP BY invNo) as income, 
             SUM(i.repairCharge) as labour,
             SUM(i.shippingCharge) as carriage 
      FROM invoices i 
      WHERE i.dateTime BETWEEN 1434326474 AND 1434361694
      GROUP BY id
     ) totals

谁能建议简化此过程并提高效率的最佳方法？

Answer 1

就大多数其他 DBMS 的易读性和性能而言，我个人只会在子查询中汇总销售记录:

SELECT  SUM(COALESCE(sr.Income, 0)) AS Income,
        SUM(i.repairCharge) AS Labour,
        SUM(i.shippingCharge) AS Carriage
FROM    Invoices AS i
        LEFT JOIN
        (   SELECT  InvNo, SUM((price-discount)*quantity) AS Income
            FROM    SalesRecords
            GROUP BY InvNo
        ) AS sr
            ON sr.InvNo = i.ID
WHERE   i.dateTime BETWEEN 1434326474 AND 1434361694;

MySQL 在子查询上使用中间物化(据我所知)，如果它这样做可能会对上述查询产生不利影响，因为它会首先汇总 SalesRecords 中的所有记录并存储在对发票日期应用过滤器之前会生成哈希表，因此您的一个版本，但使用 JOIN 而不是相关子查询可能会执行得更好:

SELECT  SUM(COALESCE(i.Income, 0)) AS Income,
        SUM(i.repairCharge) AS Labour,
        SUM(i.shippingCharge) AS Carriage
FROM    (   SELECT  i.ID,
                    i.repairCharge,
                    i.shippingCharge,
                    SUM((sr.price - sr.discount) * sr.quantity) AS Income
            FROM    Invoices AS i
                    LEFT JOIN SalesRecords AS sr
                        ON sr.InvNo = i.ID
            WHERE   i.dateTime BETWEEN 1434326474 AND 1434361694
            GROUP BY i.ID, i.repairCharge, i.ShippingCharge
        ) AS i;