mysql - 使用 SUM 和 JOIN 查询发现意外的 SUM 结果

Question

关闭。这个问题需要 details or clarity 。目前不接受答案。

---

想要改进这个问题？通过 editing this post 添加详细信息并澄清问题。

已关闭 4 年前。

奥 git _a

我有两个表“tbl_orders”和“tbl_instore”。 tbl_orders 有“sel_product_qty”，我想按日期进行 SUM，表(tbl_instore)有“inst_prod_qty”和“chln_amount”，我计算并希望获得购买的单价。但是，当我对这两个表使用联接查询时，SUM(sel_product_qty) 会产生预期结果的两倍、三倍和四倍的金额。

示例数据表是..

表“tbl_order”:

+----------+------------+------------+-----------------+---------------+--------------------+
| order_id | ord_det_id | product_id | sel_product_qty | selling_price |   order_date_time  |
+----------+------------+------------+-----------------+---------------+--------------------+
|  3       |     1      |      4     |       50        |     67.82     | 2019-03-21 21:52:21|
|  4       |     1      |      1     |       100       |     37.88     | 2019-03-21 21:52:21|
|  5       |     2      |      4     |       120       |     67.82     | 2018-03-23 00:02:36|
|  6       |     3      |      3     |       300       |     123.67    | 2019-03-23 00:04:38|
|  7       |     3      |      2     |       50        |     76.28     | 2019-03-23 00:04:38|
|  8       |     4      |      4     |       50        |     67.82     | 2019-03-24 12:13:06|
|  9       |     4      |      2     |       100       |     76.28     | 2019-03-24 12:13:06|
|  10      |     5      |      1     |       10        |     37.88     | 2019-03-25 12:56:40|
|  11      |     5      |      4     |       7         |     67.82     | 2019-03-25 12:56:40|
|  12      |     6      |      4     |       23        |     67.82     | 2019-03-29 00:29:14|
|  13      |     6      |      2     |       25        |     76.28     | 2019-03-29 00:29:14|
|  16      |     7      |      1     |       120       |     37.88     | 2019-04-14 16:51:10|
|  17      |     7      |      3     |       90        |     123.67    | 2019-04-14 16:51:11|
|  18      |     8      |      1     |       100       |     66.95     | 2019-04-22 23:30:39|
|  19      |     8      |      2     |       22        |     70.04     | 2019-04-22 23:30:39|
+----------+------------+------------+-----------------+---------------+--------------------+

表“tbl_instore”:

+----------+----------+------------+---------------+-------------+--------------------+
| in_st_id | s_inv_id | product_id | inst_prod_qty | chln_amount |  instore_date_time |
+----------+----------+------------+---------------+-------------+--------------------+
|    1     |    1     |     1      |    1000       |    65852    |  2/14/2018 17:28   |
|    14    |    9     |     1      |    100        |    6400     |  4/26/2019 8:26    |
|    3     |    2     |     1      |    2000       |    58885    |  3/19/2019 17:32   |
|    5     |    3     |     1      |    100        |    3588     |  3/19/2019 17:35   |
|    11    |    7     |     1      |    1000       |    65000    |  4/22/2019 23:17   |
|    9     |    5     |     1      |    100        |    6345     |  4/20/2019 0:13    |
|    12    |    8     |     2      |    100        |    7800     |  4/22/2019 23:20   |
|    8     |    4     |     2      |    2000       |    144567   |  3/23/2019 0:04    |
|    7     |    4     |     3      |    1000       |    121665   |  3/23/2019 0:04    |
|    13    |    8     |     3      |    150        |    32000    |  4/22/2019 23:20   |
|    15    |    9     |     3      |    100        |    19000    |  4/26/2019 8:26    |
|    10    |    6     |     4      |    1000       |    88022    |  4/20/2019 0:16    |
|    6     |    3     |     4      |    100        |    6582     |  3/19/2019 17:35   |
|    4     |    2     |     4      |    1000       |    65882    |  3/19/2019 17:32   |
|    2     |    1     |     4      |    5000       |    359877   |  2/14/2018 17:28   |
+----------+----------+------------+---------------+-------------+--------------------+

我当前尝试过以下查询:

SELECT SUM(tbl_orders.sel_product_qty) AS `sel_prod_qty`,
(SUM(chln_amount) / SUM(inst_prod_qty)) AS `pur_uni_price`, 
            date_format(`order_date_time`, '%M-%Y') as `month`, 
            tbl_orders.product_id AS `product_id`
            FROM tbl_orders
            INNER JOIN tbl_instore ON tbl_instore.product_id = tbl_orders.product_id
            WHERE YEAR(`order_date_time`)= '2019' 
            GROUP BY `month`, `product_id`;

返回以下结果:

+--------------+---------------+--------+------------+
| sel_prod_qty | pur_uni_price | month  | product_id |
+--------------+---------------+--------+------------+
|     1320     |    47.923256  | Apr-19 |     1      |
+--------------+---------------+--------+------------+
|     44       |    72.555714  | Apr-19 |     2      |
+--------------+---------------+--------+------------+
|     270      |    138.132    | Apr-19 |     3      |
+--------------+---------------+--------+------------+
|     660      |    47.923256  | Mar-19 |     1      |
+--------------+---------------+--------+------------+
|     350      |    72.555714  | Mar-19 |     2      |
+--------------+---------------+--------+------------+
|     900      |    138.132    | Mar-19 |     3      |
+--------------+---------------+--------+------------+
|     520      |    73.290563  | Mar-19 |     4      |
+--------------+---------------+--------+------------+

如果我在没有 JOIN 的情况下单独运行查询，我将得到如下 SUM(sel_prod_qty) 值(我的预期结果也应该是):

+--------------+---------------+--------+------------+
| sel_prod_qty | pur_uni_price | month  | product_id |
+--------------+---------------+--------+------------+
|     220      |    47.923256  | Apr-19 |     1      |
+--------------+---------------+--------+------------+
|     22       |    72.555714  | Apr-19 |     2      |
+--------------+---------------+--------+------------+
|     90       |    138.132    | Apr-19 |     3      |
+--------------+---------------+--------+------------+
|     110      |    47.923256  | Mar-19 |     1      |
+--------------+---------------+--------+------------+
|     175      |    72.555714  | Mar-19 |     2      |
+--------------+---------------+--------+------------+
|     300      |    138.132    | Mar-19 |     3      |
+--------------+---------------+--------+------------+
|     130      |    73.290563  | Mar-19 |     4      |
+--------------+---------------+--------+------------+

所以，我的问题是，为什么我的查询返回的 SUM(sel_product_qty) 数量是预期结果的两倍、三倍和四倍？

Answer 1

试试这个:

SELECT `sel_prod_qty`,
       `pur_uni_price`, 
       `month`, 
        orders.product_id AS `product_id`
     FROM  
        (SELECT product_id,SUM(sel_product_qty) AS `sel_prod_qty`,
                DATE_FORMAT(`order_date_time`, '%M-%Y') AS `month` 
           FROM tbl_orders 
          WHERE YEAR(`order_date_time`)='2019' 
         GROUP BY `month`, `product_id`) orders
     INNER JOIN 
         (SELECT product_id,(SUM(chln_amount) / SUM(inst_prod_qty)) AS `pur_uni_price` 
            FROM tbl_instore GROUP BY product_id) instore 
              ON orders.product_id=instore.product_id;

我从您加入的那些表中创建了两个子查询，因为不起作用的是您正在执行INNER JOIN ... ON tbl_instore.product_id = tbl_orders.product_id。如果您回顾一下您的表，这些值在两个表中都重复了多次。因此，该子查询将执行数学运算，而外部查询将仅返回其结果。