mysql - 我可以将 MySQL 中的外连接的空值替换为文本吗

Question

我正在尝试用一些文本替换查询中的 null，而不必通过代码来完成。

查询语句如下:

mysql> SELECT title, AVG(rating) AS avg_rating FROM movie m LEFT OUTER JOIN rating r ON m.movieID = r.movieID WHERE year < 1980 Group BY title;

+-------------------------------------+------------+
| title                               | avg_rating |
+-------------------------------------+------------+
| Davy Crockett and the River Pirates |     3.6667 |
| Dumbo                               |     2.0000 |
| Fantasia                            |     1.5000 |
| Rocky                               |       NULL |
| Superman                            |       NULL |
| The Godfather                       |       NULL |
| The Jungle Book                     |     4.6667 |
+-------------------------------------+------------+

是否可以使用“无评级”文本来代替 NULL？

Answer 1

我能够通过以下查询解决问题:

SELECT title,
COALESCE(AVG(rating),'NIL') AS avg_rating
FROM movie m LEFT OUTER JOIN rating r ON m.movieID = r.movieID
WHERE `year` < 1980
Group BY title

信用:@Strawberry