mysql - 需要帮助从 MySQL 中的连接表中排除某些行

Question

我有一个使用 MySQL 的游戏。我正在尝试显示最终游戏分数的列表，并且此列表应排除用户已阻止的任何人。以下是表结构:

table_game
------------
table_id    int
person_id   int
person_nom  varchar(25)
finalscore  int
scoretime   datetime

table_blocked
--------------
table_id    int
person_id   int
person_id_blocked   int

假设我的 person_id 为“1”，并且我已阻止 person_id“5”(因此 table_blocked 中有一行 table_id = 1、person_id = 1、person_id_blocked = 5)。这意味着我不应该在结果中看到 person_id '5'。

这是查询，但结果中显示了 person_id '5':

SELECT
  f.table_id,
  f.person_id as fID,
  f.person_nom AS fNOM,
  f.finalscore AS fFINAL,
  f.scoretime AS fTIME,
  table_blocked.table_id,
  table_blocked.person_id,
  table_blocked.person_id_blocked 
FROM
  table_game f 
  LEFT OUTER JOIN table_blocked ON f.person_id = table_blocked.person_id  
WHERE (
  (fFINAL > 0)
   AND ((table_blocked.person_id_blocked != '5') OR (table_blocked.person_id_blocked IS NULL))
)  
ORDER BY fTIME DESC 
LIMIT 5

我做错了什么？

Answer 1

嗯，是的。我认为编写查询的更清晰的方法是:

SELECT f.table_id, f.person_id as fID, f.person_nom AS fNOM, f.finalscore AS fFINAL,
       f.scoretime AS fTIME, table_blocked.table_id, table_blocked.person_id,   
       tb.person_id_blocked 
FROM table_game f
WHERE f.person_id not in (select person_id_blocked from table_blocked) and
      f.finalscore > 0

这将变成:

SELECT f.table_id, f.person_id as fID, f.person_nom AS fNOM, f.finalscore AS fFINAL,
       f.scoretime AS fTIME, table_blocked.table_id, table_blocked.person_id,   
       tb.person_id_blocked 
FROM table_game f LEFT OUTER JOIN
     table_blocked tb
     ON f.person_id = tb.person_id_blocked
WHERE tb.person_id_blocked is null and
      f.finalscore > 0;