如何在MySQL中使用COUNT时返回0而不是null [英] How to return 0 instead of null when using COUNT in MySQL

问题描述

我使用这个查询来返回存储在$ sTable中的歌曲列表，以及存储在$ sTable2中的总项目的COUNT。

  / * 
 * SQL查询
 *获取要显示的数据
 * / 
 $ sQuery =
 SELECT SQL_CALC_FOUND_ROWS.str_replace 
 
 FROM $ sTable b 
 LEFT JOIN（
 SELECT COUNT（*）AS projects_count， a.songs_id 
 
 FROM $ sTable2 a 
 GROUP BY a.songs_id 
）bb ON bb.songs_id = b.songsID 
 
 
 $ sWhere 
 $ sOrder 
 $ sLimit 
; 
 $ rResult = mysql_query（$ sQuery，$ gaSql ['link']）或die（mysql_error（））; 
 

'projects_count'与'$ sTable'中的列一起放入数组，然后

这是一个完美的工作，除了一首歌曲没有项目链接到它。它当然返回NULL。

我想要的是任何空值作为'0'返回。

我已尝试过COUNT（），COUNT（IFNULL（project_id，0）和使用COUNT（DISTINCT）...

>

  SELECT COALESCE（COUNT（*），0）AS projects_count，a.songs_id 
  
 
 
 
任何想法？
 c>使用 COALESCE（）函数。 COALESCE（）至少2个参数，按顺序计算，并返回第一个非空参数。 COALESCE（null，0）将返回 0 和 COALESCE（null，null，null，null，1）会返回 1  a href =http://dev.mysql.com/doc/refman/5.0/en/comparison-operators.html#function_coalesce =nofollow> MySQL的文档 about  COALESCE （）。
 
 
 
在重新阅读查询时，您应能够获得想要的结果
 选择<您想要的所有字段>，b.songsID，COUNT（*）AS projects_count 
 FROM $ sTable b 
 LEFT OUTER JOIN $ sTable2 bb ON bb.songs_id = b.songsID 
 $ sWhere 
 GROUP BY b.songsID 
 $ sOrder 
 $ sLimit 
  
像我说的，这应该可以工作，但关于它的东西不太对。
 
I am using this query to return return a list of songs stored in $sTable along with a COUNT of their total projects which are stored in $sTable2.
 /*
     * SQL queries
     * Get data to display
     */
    $sQuery = "
        SELECT SQL_CALC_FOUND_ROWS ".str_replace(" , ", " ", implode(", ", $aColumns))."

    FROM $sTable b 
    LEFT JOIN (
   SELECT COUNT(*) AS projects_count, a.songs_id

   FROM $sTable2 a
   GROUP BY a.songs_id
) bb ON bb.songs_id = b.songsID


        $sWhere
        $sOrder
        $sLimit
    ";
    $rResult = mysql_query( $sQuery, $gaSql['link'] ) or die(mysql_error());
'projects_count' is put into an array along with the columns in '$sTable', this is then spat out via JSON and displayed in a table on page.

This is working perfectly apart from when a song has no projects linked to it.  It of course returns NULL.

All I want is for any null values to be returned as a '0'.

I have tried the COUNT(), COUNT(IFNULL (project_id,0) and using COUNT(DISTINCT)...

And also:-
SELECT COALESCE(COUNT(*),0) AS projects_count, a.songs_id
All without success.

Any ideas?
解决方案
Use the COALESCE() function.  COALESCE() takes at least 2 arguments, calculated in order, and returns the first non-null argument.  So COALESCE(null, 0) would return 0, and COALESCE(null, null, null, null, 1) would return 1.  Here's MySQL's documentation about COALESCE().

In re-reading your query, you should be able to get the results you want like this:
SELECT <all the fields you want>, b.songsID, COUNT(*) AS projects_count
FROM $sTable b
LEFT OUTER JOIN $sTable2 bb ON bb.songs_id = b.songsID
$sWhere
GROUP BY b.songsID
$sOrder
$sLimit
Like I said, this should work, but something about it doesn't feel quite right.

                        
这篇关于如何在MySQL中使用COUNT时返回0而不是null的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！