时间戳

首页 > 解决方案 > 来自多个表的多行的 MySQL SUM

php - 来自多个表的多行的 MySQL SUM

问题描述

我试图从 2 个不同的表中获取多行的总和，但结果以某种方式返回多行。

我需要得到quotation_item_amount（按quotation_id分组）和invoice_item_amount（按invoice_id分组）的总和，如果我查询未付报价，我需要得到WHERE SUM(invoice) < SUM(quotation)

所以这是我的示例表

table client_project_id
+-------------------+-----------+----------------------+
| client_project_id | client_id | client_project_title |
+-------------------+-----------+----------------------+
|         23        |     5     |        Project 1     |
|         17        |     9     |        Project 2     |
|         54        |     7     |        Project 3     |
+-------------------+-----------+----------------------+

table quotation
+--------------+-------------------+------------------+
| quotation_id | client_project_id | quotation_number |
+--------------+-------------------+------------------+
|       1      |          23       |   Q/01/2020/001  |
|       2      |          17       |   Q/01/2020/002  |
|       3      |          54       |   Q/01/2020/003  |
+--------------+-------------------+------------------+

table quotation_item
+-------------------+--------------+-----------------------+
| quotation_item_id | quotation_id | quotation_item_amount |
+-------------------+--------------+-----------------------+
|         1         |     1        |          500          |
|         2         |     1        |          700          |
|         3         |     1        |          600          |
|         4         |     2        |          200          |
|         5         |     2        |          150          |
|         6         |     3        |          900          |
+-------------------+--------------+-----------------------+

table invoice
+--------------+-------------------+------------------+
|  invoice_id  | client_project_id |  invoice_number  |
+--------------+-------------------+------------------+
|       1      |          23       |   I/01/2020/001  |
|       2      |          17       |   I/01/2020/002  |
|       3      |          54       |   I/01/2020/003  |
+--------------+-------------------+------------------+

table invoice_item
+-------------------+--------------+-----------------------+
|  invoice_item_id  |  invoice_id  |  invoice_item_amount  |
+-------------------+--------------+-----------------------+
|         1         |     1        |          500          |
|         2         |     1        |          700          |
|         3         |     1        |          600          |
|         4         |     2        |          200          |
|         5         |     2        |          150          |
|         6         |     3        |          900          |
+-------------------+--------------+-----------------------+

我需要得到的结果是：

  1. 每个 client_project_id 的 quote_item_amount 和 invoice_item_amount 的总和
  2. 查询 WHERE SUM(invoice) < SUM(quotation)

这是我对查询的最新尝试

SELECT 
    SUM(quotation_item.quotation_item_amount) as quot_amt, 
    SUM(invoice_item.invoice_item_amount) as inv_amt,
    data_client_project.client_project_id,
    data_client.client_name

FROM data_client_project a
LEFT JOIN quotation b ON a.client_project_id = b.client_project_id
LEFT JOIN data_client d ON a.client_id = d.client_id
LEFT JOIN invoice i ON a.client_project_id = i.client_project_id
JOIN (
        SELECT quotation_id, 
            SUM(c.quotation_item_amount) as quot_amt
        FROM quotation_item c 
        GROUP BY c.quotation_id
      ) quotitem
        ON b.quotation_id = quotitem.quotation_id
        JOIN (
            SELECT invoice_id, 
                SUM(e.invoice_item_price) as inv_amt
            FROM invoice_item e
            GROUP BY e.invoice_id
        ) invitem
        ON i.invoice_id = invitem.invoice_id

但是，这会导致 quote_item_amount 和 invoice_item_amount 的多个重复行。

尝试使用 UNION / UNION ALL 和其他几个不起作用的查询。感谢您的所有建议。

标签： phpmysqlsql

解决方案

看起来您正试图同时沿两个不同的维度进行聚合。解决方案是沿每个维度进行预聚合：

SELECT *
FROM data_client_project cp LEFT JOIN
     (SELECT q.client_project_id,
             SUM(qi.quotation_item_amount * qi.quotation_item_qty) as quot_amt
      FROM quotation q JOIN
           quotation_item qi
           ON qi.quotation_id = q.quotation_id
      GROUP BY q.client_project_id
     ) q
     USING (client_project_id) LEFT JOIN
     (SELECT i.client_project_id,
             SUM(invoice_item_price) as inv_amt
      FROM invoice i JOIN
           invoice_item ii
           ON i.invoice_id = ii.invoice_id
      GROUP BY i.client_project_id
     ) i
     USING (client_project_id);

关于你的风格的两个注意事项。

首先，您使用任意字母作为表别名。如果您添加新表、删除表或重新排列名称，这会使查询变得非常难以理解并且变得非常尴尬。使用表的缩写。更容易遵循。

其次，我真的不推荐SELECT *这样的查询。但是，您可以通过替换为来避免重复的ON列 USING。

推荐阅读