如何在MySQL中使用COUNT时返回0而不是null

本文介绍了如何在MySQL中使用COUNT时返回0而不是null的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！ 问题描述

我使用这个查询来返回存储在$ 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_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.