MySQL 连接空值

Question

我有以下查询:

SELECT
    A.player, A.score, B.kills, C.deaths, D.killed AS most_killed, D.kills AS most_killed_count, E.player AS most_killed_by, E.kills AS most_killed_by_count
FROM
(SELECT player, score FROM pvpr_scores WHERE player = 'Neutronix' AND milestone = 'default') AS A
LEFT JOIN (SELECT player, COUNT(*) AS kills FROM pvpr_kills WHERE player = 'Neutronix' AND milestone = 'default') AS B ON B.player= A.player
LEFT JOIN (SELECT killed, COUNT(*) AS deaths FROM pvpr_kills WHERE killed = 'Neutronix' AND milestone = 'default') AS C ON C.killed= A.player
LEFT JOIN (SELECT player, killed, COUNT(*) AS kills FROM pvpr_kills WHERE player = 'Neutronix' GROUP BY killed ORDER BY kills DESC LIMIT 1) AS D ON D.player= A.player
LEFT JOIN (SELECT player, killed, COUNT(*) AS kills FROM pvpr_kills WHERE killed = 'Neutronix' GROUP BY player ORDER BY kills DESC LIMIT 1) AS E ON E.killed= A.player

但是，如果这些子查询之一返回 null，则整个查询将失败。我希望在返回的结果集中(1 行)使用空查询来使这些列为空。

如果我将 Neutronix 更改为数据库中没有的内容，例如 dfdsjf，则会出现错误。

编辑:这是我所做的修复。

SELECT
    A.player, A.score, B.kills, C.deaths, D.killed AS most_killed, D.kills AS most_killed_count, E.player AS most_killed_by, E.kills AS most_killed_by_count
FROM
(SELECT player, score FROM pvpr_scores WHERE player = 'Gutterknife' AND milestone = 'default') AS A
LEFT OUTER JOIN (SELECT COUNT(*) AS kills FROM pvpr_kills WHERE player = 'Gutterknife' AND milestone = 'default') AS B ON TRUE
LEFT OUTER JOIN (SELECT COUNT(*) AS deaths FROM pvpr_kills WHERE killed = 'Gutterknife' AND milestone = 'default') AS C ON TRUE
LEFT OUTER JOIN (SELECT killed, COUNT(*) AS kills FROM pvpr_kills WHERE player = 'Gutterknife' GROUP BY killed ORDER BY kills DESC LIMIT 1) AS D ON TRUE
LEFT OUTER JOIN (SELECT player, COUNT(*) AS kills FROM pvpr_kills WHERE killed = 'Gutterknife' GROUP BY player ORDER BY kills DESC LIMIT 1) AS E ON TRUE

Answer 1

我不认为问题出在子查询上。我认为问题出在驱动程序查询上:

FROM (SELECT player, score
      FROM pvpr_scores
      WHERE player = 'Neutronix' AND milestone = 'default'
     ) AS A . . . 

如果没有任何内容与此查询匹配，则其他查询中没有可匹配的行。

我不太确定如何解决这个问题。我想你可以做这样的事情:

FROM (select 'Neutronix' as player) t left outer join
     (SELECT player, score
      FROM pvpr_scores
      WHERE player = 'Neutronix' AND milestone = 'default'
     ) AS A
     on t.player = a.player left outer join . . . 

然后将 from 子句其余部分中的 join 条件更改为 t. 而不是 a..