donglang6656
2015-09-14 17:50
浏览 117
已采纳

Mysql IFNULL只在多个SELECT查询中工作一次

I'm trying to build a month graphic using a MySQL query. I'm checking how many rows there are in a table for each month in a single query using the UNION command. Example with 3 months bellow:

$query =
"SELECT IFNULL((SELECT SUM(score) FROM statistics WHERE MONTH(date) = 1), 0) AS total UNION
 SELECT IFNULL((SELECT SUM(score) FROM statistics WHERE MONTH(date) = 2), 0) AS total UNION
 SELECT IFNULL((SELECT SUM(score) FROM statistics WHERE MONTH(date) = 3), 0) AS total";

$stats_query = mysqli_query ($db_connection, $query);

  $result = "";
  while ($row = mysqli_fetch_assoc($stats_query)) {
    $result .= $row['total'].",";
  }
  echo ($result);

// OUTPUT: 0,176,68,

As you can see, I'm telling mysql to return me a "0" in case there are no rows for that month (which is the case for January).

There are a total of 12 SELECTS in that query (I copied just 3 to save space), one for each month. Some months will return a value, others won't (which the IFNULL should then convert to a "0"). My final output, for all the 12 months, should look like this:

// OUTPUT: 0,176,68,0,0,0,0,0,12,15,176,43,

BUT... if there is more than one SELECT that returns no rows, the query won't add another "0" to the result. My final result ends up being like this:

// OUTPUT: 0,176,68,12,15,176,43,

It's like the IFNULL is only executed once, even though he's present in all the 12 SELECTS...

Am I doing something wrong? Can anyone spot an error in my code or something?

Thank you!

图片转代码服务由CSDN问答提供 功能建议

我正在尝试使用MySQL查询构建月图形。 我正在使用UNION命令检查单个查询中每个月的表中有多少行。 下面3个月的示例:

  $ query = 
“SELECT IFNULL((SELECT SUM(score)FROM statistics WHERE MONTH(date)= 1),0)AS total  UNION 
 SELECT IFNULL((SELECT SUM(score)FROM statistics WHERE MONTH(date)= 2),0)AS total UNION 
 SELECT IFNULL((SELECT SUM(score)FROM statistics WHERE MONTH(date)= 3),  0)AS total“; 
 
 $ stats_query = mysqli_query($ db_connection,$ query); 
 
 $ result =”“; 
 while($ row = mysqli_fetch_assoc($ stats_query)){
 $ result  。= $​​ row ['total']。“,”; 
} 
 echo($ result); 
 
 //输出:0,176,68,
   
 \  n 

正如你所看到的,我告诉mysql在那个月没有行的情况下给我一个“0”(1月就是这种情况)。

该查询中总共有12个SELECTS(我只复制了3个以节省空间),每个月一个。 有些月份会返回一个值,有些则不会(因此IFNULL应该将其转换为“0”)。 我的所有12个月的最终输出应该如下所示: < pre> // OUTPUT:0,176,68,0,0,0,0,0,12,15,176,43,

但是...... 如果有多个SELECT不返回任何行,则查询不会向结果中添加另一个“0”。 我的最终结果是这样的:

  // OUTPUT:0,176,68,12,15,176,43,
   
 \  n 

这就像IFNULL只执行一次,即使他出现在所有12个SELECTS中......

我做错了吗? 任何人都可以在我的代码中发现错误吗?

谢谢!

  • 写回答
  • 好问题 提建议
  • 追加酬金
  • 关注问题
  • 收藏
  • 邀请回答

1条回答 默认 最新

  • doulangbi6869 2015-09-14 17:52
    已采纳

    Use UNION ALL instead of UNION to get all results:

     SELECT IFNULL((SELECT SUM(score) FROM statistics WHERE MONTH(date) = 1), 0) AS total UNION ALL
     SELECT IFNULL((SELECT SUM(score) FROM statistics WHERE MONTH(date) = 2), 0) AS total UNION ALL
     SELECT IFNULL((SELECT SUM(score) FROM statistics WHERE MONTH(date) = 3), 0) AS total
    

    UNION returns only DISTINCT rows.

    From doc:

    The default behavior for UNION is that duplicate rows are removed from the result.

    评论
    解决 无用
    打赏 举报

相关推荐 更多相似问题