MYSQL:View 语句产生不正确的 SUM 总数

Question

我有 3 个表。产品价格、开票产品和订购产品的表格。我正在尝试创建一个连接这些的 View 。我想输出产品价格以及开票产品总数和订购产品总数。

产品价格

id  season_id   product_id  product_price
1   1           1           3.99
2   1           2           6.99
3   1           3           5.99
4   1           4           5.99
....

invoices_products

id  season_id   invoice_id  product_id  piece_qty
1   1           1           1           1600
2   1           2           2           3200
3   1           3           2           200
4   1           4           1           120 
....

订单产品

id  season_id   order_id    product_id  piece_qty
1   1           1           1           160
2   1           2           1           40
3   1           2           2           20
4   1           3           2           10
....

---

以下是我迄今为止尝试过的 View 语句中的一些查询。

这个查询给了我想要的一切。 View 的输出是完美的，但前两行的 SUM() 已关闭。 total_invoice_product 对于第 1 行和第 2 行是两倍。total_order_product对于第 1 行是 4x，对于第 2 行是 3x。

声明 1:

SELECT 
    `t1`.`id` AS `id`,
    `t1`.`season_id` AS `season_id`,
    `t1`.`product_id` AS `product_id`,
    `t1`.`product_piece_price` AS `product_piece_price`,
    SUM(`t2`.`piece_qty`) AS `total_invoice_product`,
    SUM(`t3`.`piece_qty`) AS `total_order_product`
FROM
    ((`products_price` `t1`
    LEFT JOIN `invoices_products` `t2` ON (((`t2`.`product_id` = `t1`.`product_id`)
        AND (`t2`.`season_id` = `t1`.`season_id`))))
    LEFT JOIN `orders_products` `t3` ON (((`t3`.`product_id` = `t1`.`product_id`)
        AND (`t3`.`season_id` = `t1`.`season_id`))))
GROUP BY `t1`.`season_id` , `t1`.`product_id`

这个查询给了我预期的输出。它不是我想要的完整输出，但它对声明是正确的。 SUM() 总数在这一项上也有偏差。

声明 2:

SELECT 
    `t1`.`id` AS `id`,
    `t1`.`season_id` AS `season_id`,
    `t1`.`product_id` AS `product_id`,
    `t1`.`product_price` AS `product_price`,
    SUM(`t2`.`piece_qty`) AS `total_invoice_product`,
    SUM(`t3`.`piece_qty`) AS `total_order_product`
FROM
    ((`products_price` `t1`
    LEFT JOIN `invoices_products` `t2` ON ((`t2`.`product_id` = `t1`.`product_id`)))
    LEFT JOIN `orders_products` `t3` ON ((`t3`.`product_id` = `t1`.`product_id`)))
WHERE
    ((`t2`.`season_id` = `t1`.`season_id`)
        AND (`t2`.`product_id` = `t1`.`product_id`))
GROUP BY `t1`.`season_id` , `t1`.`product_id`

---

我想要的输出

id  season_id   product_id  product_price   total_invoice   total_order
1   1           1           3.99            1720            200
2   1           2           6.99            3400            30
3   1           3           5.99            576 
4   1           4           5.99            800 

收到语句 1 的输出

id  season_id   product_id  product_price   total_invoice   total_order
1   1           1           3.99            3440            800
2   1           2           6.99            6800            90
3   1           3           5.99            576 
4   1           4           5.99            800 

收到语句 2 的输出

id  season_id   product_id  product_price   total_invoice   total_order
1   1           1           3.99            3440            800
2   1           2           6.99            6800            90

我可以构建如下所示的查询，而且效果很好。我得到了我需要的确切输出，但此代码不能用作 View 。我收到此错误:错误 1349: View 的 SELECT 在 FROM 子句 SQL 语句中包含一个子查询

Perfect Query 但不能用作 View

SELECT 
    products_price.id,
    products_price.season_id,
    products_price.product_id,
    products_price.product_price,
    invoices_grouped.total_invoice_product,
    orders_grouped.total_order_product
FROM
    products_price
LEFT JOIN
    (SELECT 
        invoices_products.product_id,
        invoices_products.season_id,
        SUM(invoices_products.piece_qty) AS total_invoice_product
    FROM
        invoices_products
    GROUP BY 
        invoices_products.product_id) AS invoices_grouped 
    ON 
        invoices_grouped.product_id = products_price.product_id
    AND
        invoices_grouped.season_id = products_price.season_id
LEFT JOIN
    (SELECT 
        orders_products.product_id,
        orders_products.season_id,
        SUM(orders_products.piece_qty) AS total_order_product
    FROM
        orders_products
    GROUP BY 
        orders_products.product_id) AS orders_grouped
    ON 
        orders_grouped.product_id = products_price.product_id
    AND
        orders_grouped.season_id = products_price.season_id

我需要什么

我已经尝试了其他几种说法。他们要么得到更坏的结果，要么得到相同的结果。有人可以帮助我使用适当的 SUM 得到 Statement 1 吗？

为问题编辑 1

此 View 提供的信息将被大量调用。 products_price 和 invcoices_products 表不会经常更改。 orders_products 会发生很大变化。如果需要 2 个 View ，使用上面的“完美”查询或使用 2 个 View 会更有效吗？

为另一个查询编辑 2

这是我的观点陈述中的另一个查询。此查询是上面显示的语句 1 的一部分。此查询工作完美但不完整。我需要第二个 SUM 列。当您添加第二个 LEFT JOIN 时，它会破坏 SUM 总数。

SELECT 
    `t1`.`id` AS `id`,
    `t1`.`season_id` AS `season_id`,
    `t1`.`product_id` AS `product_id`,
    `t1`.`product_piece_price` AS `product_piece_price`,
    SUM(`t2`.`piece_qty`) AS `total_invoice_product`
FROM
    (`products_price` `t1`
    LEFT JOIN `invoices_products` `t2` ON (((`t2`.`product_id` = `t1`.`product_id`)
        AND (`t2`.`season_id` = `t1`.`season_id`))))
GROUP BY `t1`.`season_id` , `t1`.`product_id`

输出

id  season_id   product_id  product_price   total_invoice   
1   1           1           3.99            1720            
2   1           2           6.99            3400            
3   1           3           5.99            576 
4   1           4           5.99            800 

Answer 1

嗯，MySql 有一些限制，所以你需要为子查询创建 2 个 View 并使用它们:

create view viewInvoices
as
select season_id, product_id, sum(piece_qty) pq 
from invoices_products group by season_id, product_id

create view viewOrders
as
select season_id, product_id, sum(piece_qty) pq 
from orders_products group by season_id, product_id

select pp.id, 
       pp.season_id, 
       pp.product_id, 
       pp.product_price, 
       i.pq as total_invoice, 
       o.pq as total_order
from products_price pp

left join viewInvoices as i 
           on pp.season_id = i.season_id and pp.product_id = i.product_id

left join viewOrders as o 
           on pp.season_id = o.season_id and pp.product_id = o.product_id