mysql - 在 SQL 中从另一个表中选择一列和 SUM() 值

Question

我对 SQL 还很陌生，这给我带来了麻烦。我的想法是我有几张 table 。以下是相关的表格和列:

customers:

customer_id, customer_name


orders:

order_id, customer_id


orderline:

order_id, item_id, order_qty


items:

item_id, unit_price

我需要返回 customer_name 以及该客户的总收入(计算方式为 item_price * order_qty * 2)。

这是我写的:

SELECT customers.customer_name, sum(revenue)
    FROM SELECT orderline.order_qty * items.unit_value * 2 AS revenue
        FROM orderline
            INNER JOIN orders
            ON orderline.order_id = orders.order_id
    INNER JOIN customers
        ON revenue.customer_id = customers.customer_id;

这会引发语法错误，我不太确定如何继续。

这只是我需要解决的此类问题的一个示例，因此更通用的答案会有所帮助。

提前致谢!

编辑:

在答案的帮助下，我最终得到了这段代码，它只获取总收入并将其放在数据库名称中的第一个人旁边。我在这里犯了什么错误？

SELECT customers.customer_name, sum(revenue)
    FROM(SELECT orderline.order_qty * items.unit_price * 2 AS revenue, orders.customer_id AS CustomerID
        FROM( orderline
            INNER JOIN orders
            ON orderline.order_id = orders.order_id
                INNER JOIN items
                ON orderline.item_id = items.item_id)) CustomerOrders
    INNER JOIN customers
        ON CustomerOrders.CustomerID = customers.customer_id;

Answer 1

您的查询有几个问题。首先，您需要确定子查询的范围并为其指定别名:

(SELECT orderline.order_qty * items.unit_value * 2 AS revenue
    FROM orderline
        INNER JOIN orders
        ON orderline.order_id = orders.order_id) CustomerOrders

其次，您需要在子查询中选择比收入更多的内容，因为您要将其加入到客户表中

(SELECT 
    orderline.order_qty * items.unit_value * 2 AS revenue,
    orders.customer_id AS CustomerId
FROM 
    orderline
INNER JOIN orders ON orderline.order_id = orders.order_id) CustomerOrders

然后，您需要在与客户表的联接中使用子查询别名，并将其全部包装在按 customer_id 和 CustomerOrders.Revenue 的组中

我倾向于采取不同的做法。我首先从客户表中进行选择，因为这是您要寻找的内容的基础。然后，我会对订单进行交叉应用，以汇总子查询中的订单收入。它看起来像这样(tsql，你可以在 mysql 中通过一些聚合的连接来执行相同的操作):

SELECT
    customers.customer_name,
    ISNULL(customerOrders.Revenue, 0) AS Revenue
FROM
    customers
OUTER APPLY (
    SELECT
        SUM (orderline.order_qty * items.unit_value * 2) AS Revenue
    FROM
        orders
    INNER JOIN
        orderline ON orders.order_id = orderline.order_id
    INNER JOIN
        items on orderline.item_id = items.item_id
    WHERE
        orders.customer_id = customers.customer_id
) CustomerOrders

在这种情况下，子查询会为您聚合所有订单，并且只为每个客户返回一行，因此不会返回任何无关的数据。由于是外层apply，对于没有订单的客户也会返回null。您可以将其更改为 CROSS APPLY，它将过滤掉没有订单的客户(如 INNER JOIN)。