按sql中的monthname分组 [英] group by monthname in sql

问题描述

我有一个表格，如下所示：

I have a table which looks like:

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

• 由于我没有销售没有行军，结果必须返回march sales与数量0。



• 我在我的应用程序中使用Codeigniter，所以如果我们不能通过sql解决它可以是你可以告诉我的方式来解决它通过Codeigniter。

  • 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
  

  
  

  推荐答案

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

  I think the best option is to have a month table which looks as follows

  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
  

  将此表命名为个月：

  ，您可以使用下面的查询

  and you can use the query as below

  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 ，这将帮助您

  这篇关于按sql中的monthname分组的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！