mysql - MySQL加入空值

我有以下查询：

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

最佳答案

我认为问题不在于子查询。我认为问题是驱动程序查询：

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 . . .

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

关于mysql - MySQL加入空值，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/17757960/