sql - PostgreSQL:查找以最大订单销售的每种产品的总行总数？

Question

我是一名大学生，目前正在使用 PostgreSQL 学习数据库类(class)。有一个问题似乎给我带来了一些麻烦。

这里有两个表:

Table1-订单

字段 id、order_date、customer_id、credit_card_number、cvv 和 order_total

Table2-order_lines

Fields-id, order_id, product_id, quantity, sell_price, and line_total

问题是:

最大订单中售出的每种产品的总 line_total 是多少？ (最大的 order_total)。如果产品不是最大订单，请输入 0。确保输入两个小数位。

到目前为止，这是我的语法:

select product_id, 
sum (line_total * (1) * quantity)
from order_lines
group by product_id;

我能够输出 product_id 和 line_total 的总和，但我想知道每个产品的总 line_total 是如何以最大订单销售的。

是不是应该根据order_total找到最大订单的id？使用子查询合并两个表以获得最终答案？将 sell_price 字段与我上面的语法一起使用？

Answer 1

首先你需要获得最大的订单。这是通过获取按 order_total DESC 排序的第一条记录来完成的:

SELECT  *
FROM    orders
ORDER BY
        order_total DESC
LIMIT   1

然后您需要获取最大订单中的所有产品。只需使用 order_lines 加入它并选择不同的产品 ID:

SELECT  DISTINCT
        product_id
FROM    (
        SELECT  *
        FROM    orders
        ORDER BY
                order_total DESC
        LIMIT   1
        ) o
JOIN    order_lines ol
ON      ol.order_id = o.id

最后，我们需要将此结果集与您的查询结合起来，如果产品不是最大顺序，则用零替换总和:

SELECT  product_id,
        CASE WHEN lo.product_id IS NULL THEN sum_line_total ELSE 0 END
FROM    (
        SELECT  product_id, SUM(line_total) sum_line_total
        FROM    order_lines
        GROUP BY
                product_id
        ) ps
LEFT JOIN
        (
        SELECT  DISTINCT
                product_id
        FROM    (
                SELECT  *
                FROM    orders
                ORDER BY
                        order_total DESC
                LIMIT   1
                ) o
        JOIN    order_lines ol
        ON      ol.order_id = o.i
        ) lo
USING   (product_id)

我们可以通过不计算不是最大订单的产品的总和来进一步优化此查询，但这足以让您继续。