我使用这个查询来返回存储在$ 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 =dev.mysql/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_idAll 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 $sLimitLike I said, this should work, but something about it doesn't feel quite right.
更多推荐
如何在MySQL中使用COUNT时返回0而不是null
发布评论