获取 SUM 并生成多个 JOIN 表? [英] Getting SUM and result in several JOIN tables?

问题描述

我有这张桌子

订单

• orders_id
• order_article_id
• order_invoice_id
• order_customer_id
• 订单数量

文章

• 文章_id
• 文章名称
• 文章数量
• article_price
• article_amount

客户

• customers_id
• 客户姓名
• 客户位置
• customer_office

发票

• 发票_id
• 日期
• invoice_customer_id

和 SQL 连接表

   $sql="SELECT customer_name, article_name, orders_id, article_price, order_qty, invoices_id FROM orders  LEFT JOIN articles ON order_article_id = articles_id
            LEFT JOIN invoices ON order_invoice_id = invoices_id 
            LEFT JOIN customers  on order_customer_id = customers_id";

从这个查询我需要得到的是这个结果示例

From this query I need to get is this result example

• INVOICE_ID
• CUSTOMER_NAME
• 该发票的金额 = order_qty * article_price

是否可以在 MySQL 中做到这一点，或者我需要一些额外的 PHP 代码?

Is it possible to do that in MySQL or i need some addition PHP code?

推荐答案

您需要一个聚合查询:

SELECT c.customer_name, i.invoices_id,
       sum(order_qty * a.article_price) as amount
FROM orders o LEFT JOIN
     articles a
     ON o.order_article_id = a.articles_id LEFT JOIN
     invoices i
     ON o.order_invoice_id = i.invoices_id LEFT JOIN
     customers c
     on o.order_customer_id = c.customers_id
GROUP BY c.customer_name, i.invoice_id;

请注意，我添加了表别名以更好地指定列的来源.这使查询更容易理解.

Note that I've added table aliases to better specify where the columns come from. This makes the query easier to understand.

这篇关于获取 SUM 并生成多个 JOIN 表?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！