mysql - 在sql中按月名分组

Question

我有一张看起来像这样的表:

id    |   Item  | Quantity | Amount | created
________________________________________________
1     | Monitor | 10       | 5000   | 2013-01-11
2     | Keyboard| 10       | 200    | 2013-02-19
3     | Monitor | 10       | 5000   | 2013-02-13
3     | Keybard | 10       | 200    | 2013-04-19

当我通过查询时:

SELECT monthname( created ) AS
MONTH , sum( quantity ) AS qty
FROM `sales`
WHERE year( created ) = '2013'
GROUP BY monthname( created )
ORDER BY monthname( created ) DESC

它给出了结果:

month      |  qty    
______________________
January    | 10
February   | 20
April      | 10

但我试图检索的是:

month      |  qty    
______________________
January    | 10
February   | 20
March      | 0
April      | 10

• 因为我没有三月份的销售额，所以结果必须返回数量为 0 的三月份销售额。

• 我在我的应用程序中使用 Codeigniter，所以如果我们不能通过 sql 解决它，那么您可以告诉我通过 Codeigniter 解决它的方法。

  $this->db->select('monthname(created) as month, sum(quantity) as qty');
  $this->db->from('sales');
  $this->db->where('year(created) = 2013');
  $this->db->group_by('monthname(created)');
  $this->db->order_by('monthname(created)');
  $this->data['sales'] = $this->db->get()-result();
  

在 View 中:

$data = array();

foreach($sales as $sale) {
  $data[] = $sale->qty;
}

$data 的输出:

10, 20, 10 in array;

我需要的是

10, 20, 0, 10 array

Answer 1

我认为最好的选择是有一个如下所示的月份表

id | Month
---------------------
 1 | January
 2 | February
 3 | March
 4 | April
 5 | May
 6 | June
 7 | July
 8 | August
 9 | September
10 | October
11 | November
12 | December

将此表命名为月

您可以使用如下查询

SELECT
    months.`month`,
    Sum(sales.quantity)
FROM
    sales
RIGHT OUTER JOIN months ON months.`month` = monthname(sales.created)
GROUP BY
    months.`month`
ORDER BY
    months.id

这应该可以正常工作!

这是 SQL Fiddle对你有帮助