从3张关联表筛选符合条件的最新航班人员数据的MySQL查询问题

我尝试寻找解决方法但未成功，这应该是个基础问题，但一直没实现。现有三张表：

表结构 #

1. flightPerson表：

2. flight表：

3. country表：

查询目标 #

查询字段：personID、flightID、departure、arrive、countryID
筛选条件：

• countryID=12
• 满足以下任一条件：
  1. 用户的最新航班出发时间在2000-01-01之前
  2. 用户的最新航班出发时间在2022-03-01之后，且到达时间为0000-00-00

最初尝试的SQL语句 #

SELECT t1.personID, flight.flightID, MAX(flight.departure), flight.arrive, country.countryID
FROM flightPerson as t1
LEFT JOIN flight 
ON t1.flightID = flight.flightID 
LEFT JOIN country
ON country.personID = t1.personID 
WHERE country.countryID = 12 AND
flight.departure < "2000-01-01" OR (flight.departure > "2022-03-01" AND flight.arrive= "0000-00-00 00:00:00")  
GROUP BY personID
ORDER BY t1.personID  ASC

最终成功的SQL语句 #

基于Chris Schaller的回答，重新编写了查询语句并成功运行。核心思路是先找到每个用户的最新航班ID，再关联获取该航班的详情，最后通过WHERE子句筛选符合条件的记录。

SELECT personID 
FROM (
    SELECT DISTINCT t2.personID, country.countryID
        ,(SELECT t1.flightID
            FROM flightPerson
            LEFT JOIN flight as t1
              ON t1.flightID = flightPerson.flightID 
            WHERE t2.personID = flightPerson.personID
            ORDER BY `t1`.`departure` DESC
            LIMIT 1
        )AS lastFlightID
    FROM flightPerson as t2
    LEFT JOIN country 
    ON country.personID = t2.personID
) AS details
LEFT JOIN flight AS b
ON details.lastFlightID = b.flightID
WHERE details.countryID = 12 
    AND ( b.departure < "2000-01-01"  
        OR 
        (b.departure > "2022-03-01" AND b.arrive = "0000-00-00 00:00:00") 
        )

PS：抱歉Chris，我还是保留了双重括号AND (... OR (...) )，这样逻辑更稳妥：D

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