首页 文章

MySQL - 所有项目的每个总销售额

提问于
浏览 次
0

以下mysql查询用于获取2014年用户ID 31购买的每个项目的总销售额 .

SELECT 
    op.products_name,
    ROUND(SUM( IF(MONTH(o.date_purchased) = 1, op.final_price * op.products_quantity,0)), 2) AS january,
    ROUND(SUM( IF(MONTH(o.date_purchased) = 2, op.final_price * op.products_quantity,0)), 2) AS febraury,
    ROUND(SUM( IF(MONTH(o.date_purchased) = 3, op.final_price * op.products_quantity,0)), 2) AS march,
    ROUND(SUM( IF(MONTH(o.date_purchased) = 4, op.final_price * op.products_quantity,0)), 2) AS april,
    ROUND(SUM( IF(MONTH(o.date_purchased) = 5, op.final_price * op.products_quantity,0)), 2) AS may,
    ROUND(SUM( IF(MONTH(o.date_purchased) = 6, op.final_price * op.products_quantity,0)), 2) AS june,
    ROUND(SUM( IF(MONTH(o.date_purchased) = 7, op.final_price * op.products_quantity,0)), 2) AS july,
    ROUND(SUM( IF(MONTH(o.date_purchased) = 8, op.final_price * op.products_quantity,0)), 2) AS august,
    ROUND(SUM( IF(MONTH(o.date_purchased) = 9, op.final_price * op.products_quantity,0)), 2) AS september,
    ROUND(SUM( IF(MONTH(o.date_purchased) = 10, op.final_price * op.products_quantity,0)), 2) AS october,
    ROUND(SUM( IF(MONTH(o.date_purchased) = 11, op.final_price * op.products_quantity,0)), 2) AS november,
    ROUND(SUM( IF(MONTH(o.date_purchased) = 12, op.final_price * op.products_quantity,0)), 2) AS december
FROM orders_products op
LEFT JOIN orders o ON op.orders_id = o.orders_id
WHERE customers_id = 31
AND YEAR(o.date_purchased) = 2014
GROUP BY op.products_id
ORDER BY op.products_name

并且只有2个表可供使用 .

orders_products：orders_id，products_id，products_name ...

订单：orders_id，customers_id，date_purchased

我以为离开加入两个表会给我充分的未购买项目的NULL ...但它只给我购买的项目行 . 如何计算所有已购买/未购买商品的总销售额？

*更新

enter image description here

mysql

1 回答

  • 1

    首先你应该使用 LEFT OUTER JOIN . 你的 WHERE 条款正在减少你的结果 . 您应该删除它的 WHERE 子句或添加如下内容：

    SELECT 
op.products_name,
ROUND(SUM( IF(MONTH(o.date_purchased) = 1, op.final_price * op.products_quantity,0)), 2) AS january,
ROUND(SUM( IF(MONTH(o.date_purchased) = 2, op.final_price * op.products_quantity,0)), 2) AS febraury,
ROUND(SUM( IF(MONTH(o.date_purchased) = 3, op.final_price * op.products_quantity,0)), 2) AS march,
ROUND(SUM( IF(MONTH(o.date_purchased) = 4, op.final_price * op.products_quantity,0)), 2) AS april,
ROUND(SUM( IF(MONTH(o.date_purchased) = 5, op.final_price * op.products_quantity,0)), 2) AS may,
ROUND(SUM( IF(MONTH(o.date_purchased) = 6, op.final_price * op.products_quantity,0)), 2) AS june,
ROUND(SUM( IF(MONTH(o.date_purchased) = 7, op.final_price * op.products_quantity,0)), 2) AS july,
ROUND(SUM( IF(MONTH(o.date_purchased) = 8, op.final_price * op.products_quantity,0)), 2) AS august,
ROUND(SUM( IF(MONTH(o.date_purchased) = 9, op.final_price * op.products_quantity,0)), 2) AS september,
ROUND(SUM( IF(MONTH(o.date_purchased) = 10, op.final_price * op.products_quantity,0)), 2) AS october,
ROUND(SUM( IF(MONTH(o.date_purchased) = 11, op.final_price * op.products_quantity,0)), 2) AS november,
ROUND(SUM( IF(MONTH(o.date_purchased) = 12, op.final_price * op.products_quantity,0)), 2) AS december
FROM orders_products op
LEFT OUTER JOIN orders o ON op.orders_id = o.orders_id
WHERE (customers_id = 31 
AND YEAR(o.date_purchased) = 2014) 
OR o.date_purchased IS NULL
GROUP BY op.products_id
ORDER BY op.products_name
    回复于

相关问题