在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

• Since I have no sales no march the result must return march sales with quantity 0.

• I am using Codeigniter in my application so if we can't solve it through sql then may be you can show me the way to solve it through 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();
  

在视图中：

$data = array();

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

$data的输出结果：

10, 20, 10 in array;

我需要的是：

10, 20, 0, 10 array

- super global user

---

请点击此处查看有关如何使用MySQL选择按月记录的内容，即使数据不存在的问题。 - Abhik Chakraborty

使用SQL查询每个月的一条记录，并计算该月记录的总和。 - Roman Pickl

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

将这张表命名为months

您可以使用以下查询：

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，会对你有所帮助。

- Guns