MySQL 按产品 ID 分组，但不是具有序列号的产品 [英] MySQL Group By Product Id BUT NOT Product that has a Serial Number

问题描述

有人可以帮助我如何为此进行有效的 mysql 查询吗?

Can someone help me how to make an efficient mysql query for this?

我需要查询以获取所有订购商品的总数量.

I need to make a query to get the sum quantity of all ordered items.

结合.

我需要查询以获取所有收到的物品的总数量.

I need to make a query to get the sum quantity of all received items.

请注意，在我的received_po_details"表中有些产品在收到时有序列号，有些没有.这就是我遇到问题的地方，因为收到的总和因为序列号而翻了一番.

Please note that some products have serial numbers when received and some don't in my 'received_po_details' table. That's where I'm having problem with since the sum of received gets doubled because of the serial numbers.

表:receive_po_details

Table: received_po_details

i_rpoh_id   i_p_id  i_quantity_received s_product_serial
1           1       100 
1           2       100 
1           3       50  
1           4       25  
1           7       100 
1           8       50  
1           6       1                   XYZ1
1           6       1                   XYZ2
1           5       1                   ABC1
1           5       1                   ABC2

现在我需要合并这两个单独的 sql.如果可能的话，我不想为此使用 Union 语句...

Right now I have these 2 separate sql that I need to combine. I don't want to use Union statement for this if possible...

-- 获取订购的总数量

-- to get the Total Quantity Ordered

SELECT
    products.i_id AS 'ID',
    products.s_name AS 'Name',
    COALESCE(SUM(purchase_order_details.i_quantity_ordered),0) AS 'Total Quantity Ordered'
FROM
    products
LEFT JOIN
    purchase_order_details
    ON
    products.i_id = purchase_order_details.i_p_id
GROUP BY
    products.i_id   

-- to get the Total Quantity Received
SELECT
    products.i_id AS 'ID',
    products.s_name AS 'Name',
    COALESCE(SUM(received_po_details.i_quantity_received),0) AS 'Total Quantity Received'
FROM
    products
LEFT JOIN
    received_po_details
    ON
    products.i_id = received_po_details.i_p_id
GROUP BY
    products.i_id

推荐答案

您可以通过创建派生表或每种类型的总和来组合这些查询(在下面的查询中，它们被命名为 t1 和t2) 并将派生表连接到主产品表.

You can combine these queries by creating a derived table or each type of sum (in the query below they're named t1 and t2) and left joining the derived tables to the main product table.

SELECT
    p.i_id AS 'ID',
    p.s_name AS 'Name',
    COALESCE(t1.total,0) 'Total Quantity Ordered',
    COALESCE(t2.total,0) 'Total Quantity Received'
FROM products p
LEFT JOIN (
    SELECT 
        pod.i_p_id,
        SUM(pod.i_quantity_ordered) total
    FROM purchase_order_details pod
    GROUP BY pod.i_p_id
) t1 ON t1.i_p_id = p.i_id
LEFT JOIN (
    SELECT 
        rpd.i_p_id,
        SUM(rpd.i_quantity_received) total
    FROM received_po_details rpd
    GROUP BY rpd.i_p_id
) t2 ON p.i_id = t2.i_p_id

这篇关于MySQL 按产品 ID 分组，但不是具有序列号的产品的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！