mysql - 即使没有结果，也从 Mysql 数据库获取按月计数

Question

我正在创建图表以显示 Mysql 数据库中的月份计数。

执行以下查询:

$cash_query =  $this->db->query("select COUNT(*) as count_monthwise, MONTH(FROM_UNIXTIME(dt_added)) as month from `order` where user_id='1'  and status != '9' and payment_type = '1' GROUP BY month");
$cash_result = $cash_query->result();

输出:

Array
(
    [0] => stdClass Object
        (
            [count_monthwise] => 1
            [month] => 8
        )

    [1] => stdClass Object
        (
            [count_monthwise] => 2
            [month] => 9
        )

)

在上面的输出中，显示“count_monthwise”表示计数，月份“8”表示“第 8 个月 - 八月”。

但我想显示所有月份的输出，如果任何月份中的查找计数为 0，则显示 [count_monthwise] => 0。

我想显示精确的输出，例如:

    Array
    (
        [0] => stdClass Object
            (
                [count_monthwise] => 1
                [month] => 1
            )
        [1] => stdClass Object
            (
                [count_monthwise] => 1
                [month] => 2
            )
            .
            .
            .
            .
            .
            .
            . 
        [10] => stdClass Object
            (
                [count_monthwise] => 0
                [month] => 11
            )
        [11] => stdClass Object
            (
                [count_monthwise] => 0
                [month] => 12
            )

    )

我使用过类似这样的 foreach 循环，但这不起作用。

循环

foreach($cash_result as $cash => $cash_value){
   for($i=0;$i<=11;$i++){
      if($i == $cash){
      }
   }
}

Answer 1

这可能是一种解决方法:

SELECT 
 COALESCE(yourQuery.count_monthwise,0) AS monthwise_count,
 allMonths.month
(SELECT 1 AS month UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9 UNION SELECT 10 UNION SELECT 11 UNION SELECT 12 ) AS allMonths
LEFT JOIN
(
 SELECT 
  COUNT(*) as count_monthwise, 
  MONTH(FROM_UNIXTIME(dt_added)) as month 
 from `order` 
 where user_id='1'  and status != '9' and payment_type = '1' 
 GROUP BY month
) AS yourQuery 
ON allMonths.month = yourQuery.month