如果为空，则返回0

I have 3 tables, shown below:

我有3张表，如下所示:

mysql> select * from Raccoon;
+----+------------------+----------------------------------------------------------------------------------------------------+
| id | name             | image_url                                                                                          |
+----+------------------+----------------------------------------------------------------------------------------------------+
|  3 | Jesse Coon James | http://www.pawfun.com/wp/wp-content/uploads/2010/01/rabbid.png                                     |
|  4 | Bobby Coon       | https://c2.staticflickr.com/6/5242/5370143072_dee60d0ce2_n.jpg                                     |
|  5 | Doc Raccoon      | http://images.encyclopedia.com/utility/image.aspx?id=2801690&imagetype=Manual&height=300&width=300 |
|  6 | Eddie the Rac    | http://www.felid.org/jpg/EDDIE%20THE%20RACCOON.jpg                                                 |
+----+------------------+----------------------------------------------------------------------------------------------------+
4 rows in set (0.00 sec)

mysql> select * from Review;
+----+------------+-------------+---------------------------------------------+--------+
| id | raccoon_id | reviewer_id | review                                      | rating |
+----+------------+-------------+---------------------------------------------+--------+
|  1 |          3 |           1 | This raccoon was a fine raccoon indeed.     |      5 |
|  2 |          5 |           2 | This raccoon did not do much for me at all. |      2 |
|  3 |          3 |           1 | asdfsadfsadf                                |      5 |
|  4 |          5 |           2 | asdfsadf                                    |      1 |
+----+------------+-------------+---------------------------------------------+--------+
4 rows in set (0.00 sec)

mysql> select * from Reviewer;
+----+---------------+
| id | reviewer_name |
+----+---------------+
|  1 | Kane Charles  |
|  2 | Cameron Foale |
+----+---------------+
2 rows in set (0.00 sec)

I'm trying to build a select query that will return all of the columns in Raccoon as well as an extra column which grabs an average of Review.rating (grouped by id). The problem I face is that there is no guarantee that there will be rows present in the Review table for every single Raccoon (as determined by the FK, raccoon_id which references Raccoon.id. In situations where there are zero rows present in the Review table (for a given Raccoon.id, ie Review.raccoon_id) I'd like the query to return 0 as the average for that Raccoon.

我正在尝试构建一个select查询，该查询将返回Raccoon中的所有列，以及一个额外的列，该列获取平均的评审。评级(分组通过id)。我面临的问题是，不能保证每个浣熊都有行出现在检查表中(由引用浣熊.id的FK、raccoon_id确定)。在检查表中有零行的情况下(对于给定的浣熊来说)。id，即Review.raccoon_id)我希望查询返回0作为那个Raccoon的平均值。

Below is the current query I'm using:

下面是我正在使用的当前查询:

mysql> SELECT *, (SELECT IFNULL(AVG(rating),0) FROM Review WHERE raccoon_id=Raccoon.id GROUP BY raccoon_id) AS "AVG" FROM Raccoon ORDER BY "AVG" ASC;
+----+------------------+----------------------------------------------------------------------------------------------------+--------+
| id | name             | image_url                                                                                          | AVG    |
+----+------------------+----------------------------------------------------------------------------------------------------+--------+
|  3 | Jesse Coon James | http://www.pawfun.com/wp/wp-content/uploads/2010/01/rabbid.png                                     | 5.0000 |
|  4 | Bobby Coon       | https://c2.staticflickr.com/6/5242/5370143072_dee60d0ce2_n.jpg                                     |   NULL |
|  5 | Doc Raccoon      | http://images.encyclopedia.com/utility/image.aspx?id=2801690&imagetype=Manual&height=300&width=300 | 1.5000 |
|  6 | Eddie the Rac    | http://www.felid.org/jpg/EDDIE%20THE%20RACCOON.jpg                                                 |   NULL |
+----+------------------+----------------------------------------------------------------------------------------------------+--------+
4 rows in set (0.00 sec)

As you can see above, the query isn't returning 0 for Raccoons with id of 4 and 6, it is simply returning NULL. I need it to return something like the following (note the ordering, sorted by lowest average review first):

正如您在上面看到的，对于id为4和6的浣熊，查询不会返回0，它只是返回NULL。我需要它返回如下内容(请注意排序，首先按最低平均评审进行排序):

+----+------------------+----------------------------------------------------------------------------------------------------+--------+
| id | name             | image_url                                                                                          | AVG    |
+----+------------------+----------------------------------------------------------------------------------------------------+--------+
|  4 | Bobby Coon       | https://c2.staticflickr.com/6/5242/5370143072_dee60d0ce2_n.jpg                                     | 0.0000 |
|  6 | Eddie the Rac    | http://www.felid.org/jpg/EDDIE%20THE%20RACCOON.jpg                                                 | 0.0000 |
|  5 | Doc Raccoon      | http://images.encyclopedia.com/utility/image.aspx?id=2801690&imagetype=Manual&height=300&width=300 | 1.5000 |
|  3 | Jesse Coon James | http://www.pawfun.com/wp/wp-content/uploads/2010/01/rabbid.png                                     | 5.0000 |
+----+------------------+----------------------------------------------------------------------------------------------------+--------+

3 个解决方案

#1

use IFNULL outside your subquery since it will return null it there is not match on the outer table,

在子查询之外使用IFNULL因为它会返回null它在外部表上不匹配，

IFNULL((SELECT AVG(rating) FROM Review WHERE raccoon_id=Raccoon.id GROUP BY raccoon_id), 0) AS "AVG"

Or you can also use LEFT JOIN,

或者你也可以用左连接，

SELECT  ra.id, ra.name, ra.image_url,
        IFNULL(AVG(rv.rating),0)AS "AVG" 
FROM    Raccoon ra
        LEFT JOIN Review rv
            ON rv.raccoon_id = ra.id
GROUP   BY ra.id, ra.name, ra.image_url  
ORDER   BY "AVG" ASC;

#2

You don't want a group by in the subquery. This is dangerous because it could return more than one row (although the where prevents this). More importantly, with no group by, the subquery is an aggregation query that always returns one row. So, you can put the logic in the subquery:

您不希望在子查询中包含组。这是危险的，因为它可能返回多个行(尽管where防止这种情况发生)。更重要的是，在没有组的情况下，子查询是一个总是返回一行的聚合查询。所以，你可以把逻辑放在子查询中:

SELECT r.*,
       (SELECT COALESCE(AVG(rev.rating),0)
        FROM Review rev
        WHERE rev.raccoon_id = r.id
       ) AS "AVG"
FROM Raccoon r
ORDER BY "AVG" ASC;

Also: always use qualified column names when you have a correlated subquery. This is a good practice to prevent problems in the future.

同样:当有关联的子查询时，总是使用限定的列名。这是预防未来问题的良好做法。

#3

You can try the following SQL statement with the built-in function COALESCE():

您可以尝试使用内置函数COALESCE()的SQL语句:

SELECT *,  COALESCE((SELECT AVG(rating) FROM Review WHERE raccoon_id=Raccoon.id GROUP BY raccoon_id), 0) AS "AVG" FROM Raccoon ORDER BY "AVG" ASC;

You can find the manual of this function here .

你可以在这里找到这个函数的手册。

If you prefer to use IFNULL, you can use

如果您喜欢使用IFNULL，可以使用

SELECT *, IFNULL((SELECT AVG(rating) FROM Review WHERE raccoon_id=Raccoon.id GROUP BY raccoon_id), 0) AS "AVG" FROM Raccoon ORDER BY "AVG" ASC;

Looks like you misunderstood the scope of the functions.

看起来您误解了函数的范围。

However, I think a better way is to use left outer join, instead of a sub query, here is the query I wrote for your reference:

但是，我认为更好的方法是使用左外连接，而不是子查询，以下是我为您编写的查询:

select Raccoon.id, Raccoon.name, Raccoon.image_url, IFNULL(AVG(rating),0) avg from Raccoon LEFT OUTER join Review ON raccoon_id =Raccoon.id GROUP BY raccoon.id ORDER BY AVG ASC;

Then you will get the following results:

然后你会得到以下结果:

(sorry I don't know how I can post the output of this query, then I took a screenshot :-)

(对不起，我不知道如何发布这个查询的输出，然后我截屏:-)

Hope this will help.

希望这将帮助。

#1