如何避免WHERE匹配同一行？船舶多船员条件筛选需求

问题背景 #

现有ships和bridge_officers两张表，一艘舰船对应多名船员。需要筛选出存在两名不同船员的舰船：其中一名船员满足profession = :aprofession，另一名船员满足rank = :brank，排除同一船员同时满足两个条件的情况。

解决方案1：关联时排除同一船员 #

直接在JOIN条件中添加判断，确保两次关联的是不同的船员，同时改用INNER JOIN（因为我们需要确实存在符合条件的船员，LEFT JOIN无意义）：

SELECT a.id
FROM ships AS a 
INNER JOIN bridge_officers AS aBoff ON a.id = aBoff.ship_id
INNER JOIN bridge_officers AS bBoff ON a.id = bBoff.ship_id
WHERE aBoff.profession = :aprofession 
  AND bBoff.rank = :brank
  AND aBoff.id != bBoff.id -- 强制两个关联的是不同船员
GROUP BY a.id

解决方案2：聚合统计验证（更灵活） #

通过GROUP BY结合HAVING子句，统计满足不同条件的船员数量，确保两种条件分别由不同船员满足：

SELECT a.id
FROM ships AS a
INNER JOIN bridge_officers AS bo ON a.id = bo.ship_id
WHERE bo.profession = :aprofession OR bo.rank = :brank
GROUP BY a.id
HAVING -- 确保至少有一名船员符合职业条件
       COUNT(DISTINCT CASE WHEN bo.profession = :aprofession THEN bo.id END) >= 1
       -- 确保至少有一名船员符合军衔条件
       AND COUNT(DISTINCT CASE WHEN bo.rank = :brank THEN bo.id END) >= 1
       -- 确保满足条件的船员总数至少为2（排除同一船员同时满足两个条件的情况）
       AND COUNT(DISTINCT bo.id) >= 2

这种方法的优势在于可以轻松扩展到更多筛选条件，比如需要三名不同船员分别满足三个条件时，只需在HAVING中添加对应统计项，并调整总计数要求。

内容的提问来源于stack exchange，提问作者NicholasJohn16