mysql - 获取在不同表中形成的父类别总和(价格)

Question

我正在尝试获取特定月份和年份中所有父类别的总价格。父类别是 parent_id == 0 的任何类别。我的查询如下所示:

SELECT
    ROUND(SUM(od.total_price)) as price, 
    c.parent_id as pId,
    c1.name
FROM a_orders o 
    INNER JOIN a_order_details as od on o.id = od.order_id 
    INNER JOIN a_product as p on p.id = od.product_id 
    INNER JOIN a_category as c on c.id = p.category_id
    LEFT OUTER JOIN a_category c1 ON c.parent_id = c1.id
WHERE YEAR(order_date) = 2018
    AND o.STAT = 'Y' 
    AND MONTH(order_date) = 6
GROUP BY c.parent_id;

我正在获取有价格的父类别，但我需要获取所有父类别，如果没有价格结果应该为 0。

我的 sqlfiddle 是 -> http://sqlfiddle.com/#!9/b9f4c1/1

我的结果是这样的:

price   pId  name
410     1    T-SHIRT
400     2    JEANS

但应该是这样的:

price   pId  name
410     1    T-SHIRT
400     2    JEANS
0       6    SHOES

Answer 1

为此，您需要切换逻辑。首先选择您所有的父类别，然后加入您的订单数据。这样您就可以确保所有需要的类别都将包含在结果中。

有很多方法可以解决这个问题——这里我创建了一个包含所有订单数据的子查询，然后是一个包含类别表的 LEFT JOIN:

SELECT
    IFNULL(ROUND(SUM(orders.total_price)),0) AS price, 
    c.id AS pId,
    c.name
FROM a_category c
    LEFT JOIN (
        SELECT od.total_price, c.parent_id
        FROM a_orders o 
            INNER JOIN a_order_details od ON o.id = od.order_id 
            INNER JOIN a_product p ON p.id = od.product_id 
            INNER JOIN a_category c ON c.id = p.category_id
        WHERE YEAR(order_date) = 2018
            AND o.STAT = 'Y' 
            AND MONTH(order_date) = 6
    ) orders ON orders.parent_id = c.id
WHERE c.parent_id = 0
GROUP BY c.id;

http://sqlfiddle.com/#!9/b9f4c1/3