mysql - 按周获取产品销量，如 week1、week2、week3、week4

Question

我需要制作这样的销售报告:

Product Name         First Week   Second Week  Third Week
   a                      12            6           2
   b                       0            0           4 
   c                       0            3           0  

为此，我创建了一个返回 sku 和 sum(qty_ordered) 的函数，如下所示

   $query = Mage::getResourceModel('sales/order_item_collection');

   $query->getSelect()->reset(Zend_Db_Select::COLUMNS)

    ->columns(array('name','qty_ordered'))
    ->where(new Zend_Db_Expr('created_at BETWEEN NOW()-INTERVAL 1 WEEK AND NOW()'))
    ->group(array('sku'));

   $query->getSelect()->columns(array(
        'week1' => 'SUM(main_table.qty_ordered)')
    );

上面的sql查询是:

 `SELECT `main_table`.`sku`, `main_table`.`qty_ordered`, 
 SUM(main_table.qty_ordered) AS `week1` 
 FROM `sales_flat_order_item` AS `main_table` 
  WHERE (created_at BETWEEN NOW()-INTERVAL 1 WEEK AND NOW()) 
 GROUP BY `sku 

但是上面的返回时间只有1周。如果我将 created_at BETWEEN NOW()-INTERVAL 1 WEEK AND NOW() 更改为 created_at BETWEEN NOW()-INTERVAL 2 WEEK AND NOW() 那么它会返回 2 周sales..但我想要如上格式所示的结果。那么我该如何定制这个。帮我解决这个问题。

Answer 1

`SELECT `main_table`.`sku`, `main_table`.`qty_ordered`, 
 SUM(main_table.qty_ordered) AS `week1`, 
 SUM(if (created_at BETWEEN NOW()-INTERVAL 2 WEEK AND NOW(), main_table.qty_ordered,0)) AS `week2`, 
 SUM(if (created_at BETWEEN NOW()-INTERVAL 3 WEEK AND NOW(), main_table.qty_ordered,0)) AS `week3` 

 FROM `sales_flat_order_item` AS `main_table` 
  WHERE (created_at BETWEEN NOW()-INTERVAL 3 WEEK AND NOW()) 
 GROUP BY `sku`