MySQL:当没有找到行时添加默认值到join表

Question

系统信息:

$ uname -srvm
Linux 5.15.0-56-generic #62-Ubuntu SMP Tue Nov 22 19:54:14 UTC 2022 x86_64
$ mysql --version
mysql  Ver 8.0.31-0ubuntu0.22.04.1 for Linux on x86_64 ((Ubuntu))

我对MySQL非常缺乏经验&半个星期以来我一直在寻找这个问题的答案。我正在使用两个名为character_stats&我想在查询中加入的halloffame。它们是这样的:

mysql> SELECT name, level FROM character_stats;
+-----------+-------+
| name      | level |
+-----------+-------+
| foo       |     0 |
| bar       |     0 |
| baz       |     3 |
| tester    |     4 |
| testertoo |     2 |
+-----------+-------+
mysql> SELECT * from halloffame;
+----+-----------+----------+--------+
| id | charname  | fametype | points |
+----+-----------+----------+--------+
|  1 | bar       | T        |      0 |
|  2 | foo       | T        |      0 |
|  3 | baz       | T        |      0 |
|  4 | tester    | T        |      0 |
|  5 | testertoo | T        |      0 |
|  6 | tester    | D        |     40 |
|  7 | tester    | M        |     92 |
|  8 | bar       | M        |     63 |
+----+-----------+----------+--------+

在我的查询中，我想显示character_stats&我想从halloffame到fametype='M'加入points专栏。如果没有fametype='M'的行，我想将points设置为该字符名称的0，而不是像下面所做的那样省略整行:

mysql> SELECT name, level, points FROM character_stats JOIN
-> (SELECT charname, points FROM halloffame WHERE fametype='M')
-> AS hof ON (hof.charname=name);
+--------+-------+--------+
| name   | level | points |
+--------+-------+--------+
| tester |     4 |     92 |
| bar    |     0 |     63 |
+--------+-------+--------+

我想让它输出如下内容:

+-----------+-------+--------+
| name      | level | points |
+-----------+-------+--------+
| foo       |     0 |      0 |
| bar       |     0 |     63 |
| baz       |     3 |      0 |
| tester    |     4 |     92 |
| testertoo |     2 |      0 |
+-----------+-------+--------+

我试着学习如何使用IFNULL,IF-THEN-ELSE,CASE,COALESCE， &从我在文档中发现的COUNT语句&答案在stackoverflow.com上。但就像我说的，我很没有经验。不知道如何实现它们

下面的代码可以独立运行:

SELECT IFNULL((SELECT points FROM halloffame WHERE fametype='M'
AND charname='foo' LIMIT 1), 0) as points;

但是我不知道如何将它连接到character_stats表。如果我知道如何在COALESCE被调用之前获得character_stats.name的值，下面的代码将起作用:

SELECT name, level, 'M' AS fametype, points FROM character_stats
JOIN (SELECT COALESCE((SELECT points FROM halloffame WHERE
fametype='M' AND charname=name LIMIT 1), 0) AS points) AS hof;

根据添加默认值连接表我应该能够使用CROSS JOIN，但我做错了，因为它仍然导致Unknown column 'cc.name' in 'where clause':

SELECT name, level, points FROM character_stats
CROSS JOIN (SELECT DISTINCT name FROM character_stats) AS cc
JOIN (SELECT COALESCE((SELECT points FROM halloffame WHERE
fametype='M' AND charname=cc.name LIMIT 1), 0) AS points) AS hof;

我看过一些参考资料:

• 即使没有结果也返回值
• MySQL's "IF EXISTS">
• 如果没有找到行返回默认值
• MySQL . .返回& # 39;1 & # 39;如果COUNT返回大于0的值
如何在MySQL查询中写入IF ELSE语句
• SELECT查询空结果的简单检查
• 在MySQL中是否有一个相当于Oracle's NVL的函数?
MySQL: COALESCE
• Where子句With Join
• 添加连接表的默认值
• https://www.tutorialspoint.com/returning-a-value-even-if-there-is-no-result-in-a-mysql-query

Answer 1

我发现我可以做到以下几点:

SELECT name, level, COALESCE((SELECT points FROM
halloffame WHERE fametype='M' AND charname=name
LIMIT 1), 0) AS points FROM character_stats;

虽然我仍然想知道如何在JOIN语句中做到这一点。