MySQL - 多对多关系求和

Question

我有以下两个表:

ORDERS_ENTRIES

orders_id | products_id | entry_id | quantity
1111 | 14492 | 1 | 1
1112 | 14492 | 1 | 3

还有

PRODUCT_STOCKS

products_id | entry_id | quantity
14492 | 1 | 4
14492 | 1 | 2

我试图通过以下查询找出 SUM(ORDERS_ENTRIES.quantity) 以及 products_stocks 中的数量:

SELECT 
    oe.entry_id as entry,
    SUM(oe.quantity) as qty,
    ps.quantity as ps_qty
FROM
    ORDERS_ENTRIES oe
    INNER JOIN
        PRODUCT_STOCKS ps
            ON 
                oe.entry_id = ps.entry_id;
WHERE
    oe.products_id = 14492;
GROUP BY
    ps.entry_id;

但是由于多对多的关系，返回的结果会多次统计金额。

预期答案:

oe.entry_id | oe.quantity | ps.quantity
1 | 4 | 4
1 | 4 | 2

但我得到以下答案:

oe.entry_id | oe.quantity | ps.quantity
1 | 8 | 4
1 | 8 | 2

我该怎么做？

Answer 1

根据观察，您似乎希望首先聚合 ORDERS_ENTRIES 中的数量。表格由entry_id ，然后将此结果加入 PRODUCT_STOCKS表:

SELECT oe.entry_id,
       oe.qty,
       ps.quantity AS ps_qty
FROM
(
    SELECT entry_id, SUM(quantity) AS qty
    FROM ORDERS_ENTRIES
    GROUP BY entry_id
) oe
INNER JOIN PRODUCT_STOCKS ps
    ON oe.entry_id = ps.entry_id

输出:

此处演示:

Rextester