MySQL：从 n 个关系表中 Select 所有具有 MAX() 值的行

对此有两种常见的解决方案，最简单的解释是使用ROW_NUMBER() window query Select Last Flight而不是group by.在MySQL V8中，我们可以使用CTE来帮助保持查询的可读性:

WITH PersonFlightData as (
  SELECT t1.personID, flight.flightID, flight.departure, flight.arrive, country.countryID
  , ROW_NUMBER() OVER(PARTITION BY t1.personID ORDER BY flight.departure DESC) as RN
  FROM flightPerson as t1
  LEFT JOIN flight ON t1.flightID = flight.flightID 
  LEFT JOIN country ON country.personID = t1.personID 
  WHERE country.countryID = 12 
)
SELECT personID, flightID, departure, arrive, countryID
FROM PersonFlightData
WHERE RN = 1 --(filters to only include the LAST flight for each person)
  AND (departure < '2000-01-01' OR departure > '2022-03-01' AND arrive = '0000-00-00')
ORDER BY personID ASC

遗憾的是，对于您提供的数据集，此查询没有结果.让我们删除departure筛选器以了解，实际上，让我们将departure筛选器移动到 Select 列以将其投影到输出中:

WITH PersonFlightData as (
  SELECT t1.personID, flight.flightID, flight.departure, flight.arrive, country.countryID
  , ROW_NUMBER() OVER(PARTITION BY t1.personID ORDER BY flight.departure DESC) as RN
  FROM flightPerson as t1
  LEFT JOIN flight ON t1.flightID = flight.flightID 
  LEFT JOIN country ON country.personID = t1.personID 
  WHERE country.countryID = 12 
)
SELECT personID, flightID, departure, arrive, countryID
  , CASE WHEN departure < '2000-01-01' OR departure > '2022-03-01' AND arrive = '0000-00-00' THEN 1 END as Output
FROM PersonFlightData
WHERE RN = 1 --(filters to only include the LAST flight for each person);

轻松查看此证明:https://www.db-fiddle.com/f/jKsg1B5RjW5UhTsLbtQHwe/0使用其他数据更新那里的模式，以查看是否包括您想要的航班.

因此，personID=1航班的最后一次飞行时间是2022-01-02，不在要求的范围内.personID=51个航班被排除在外，因为他们的国家是27，但他们最后一次起飞的日期，即使它仍然没有降落；)不在过滤范围内.

旧版本

对于较旧的版本，我们不能使用CTE或row_number()窗口函数，所以让我们回到使用GROUP BY.

GROUP BY的问题在于，您需要首先应用分组来确定Last Flight，然后只需要将过滤器应用于Last Flight查询的结果，尽管这似乎是合乎逻辑的.这是使用CTE也为我们解决的问题之一，在本例中，我们将不得不使用嵌套查询:

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

您可以看到，在这个查询中，我们只获得了最后一次航班的departure，这对于连接到查询的其余部分来说效率不是很高，我更愿意获得最后一次航班的ID并使用它，但要获得ID将需要不同类型的子查询，这可能更低效率，it is certainly increasing in complexity and becoming harder to read:

SELECT personID, flightID, departure, arrive, countryID, LastFlightID
FROM (
  SELECT fp.personID, fp.flightID, f.departure, f.arrive, country.countryID
    , (SELECT flight.flightID
       FROM flight
       LEFT JOIN flightPerson ON flight.flightID = flightPerson.flightID
       WHERE flightPerson.personID = fp.personID
       ORDER BY departure DESC
       LIMIT 1
    ) as LastFlightID
  FROM flightPerson fp
  LEFT JOIN flight f ON fp.flightID = f.flightID
  LEFT JOIN country ON country.personID = fp.personID 
) flightData
WHERE countryID = 12 
  AND flightID = LastFlightID
  AND (f.departure < '2000-01-01' OR f.departure > '2022-03-01' AND f.arrive = '0000-00-00')
ORDER BY personID ASC;

在MySQL 5.6中查看此小提琴:http://sqlfiddle.com/#!9/a8e82d/3

Finally, a special note about the OR clause...

您的原始筛选表达式:

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")

漏掉了一些方括号，我想您知道这一点，因为您的解释中的方括号是正确的:

其中，Country ID=12 and(上次航班是2000-01-01之前或最后一次航班是(2022-03-01之后，到达时间是0000-00-00))

我喜欢做的事情(因为我首先是一名应用程序开发人员)是使用缩进来直观地分隔括号内的内容，以便更容易地识别子表达式.

WHERE countryID = 12 
  AND (
        LAST FLIGHT was before 2000-01-01 
        OR LAST FLIGHT was (after 2022-03-01 AND arrive time is 0000-00-00)
  )

但在本例中，内括号不是必需的，因为OR语句将独立计算OR两边的表达式:

WHERE country.countryID = 12 
  AND (flight.departure < '2000-01-01' OR flight.departure > '2022-03-01' AND flight.arrive= '0000-00-00')

这句话只有一行，但也许更容易读懂:

WHERE country.countryID = 12 
  AND (
       flight.departure < '2000-01-01' 
       OR flight.departure > '2022-03-01' AND flight.arrive= '0000-00-00'
  )

FYI:这里涉及X个表这一事实并不是真正复杂的因素.问题是，您希望对分组的结果应用额外的逻辑，而不是该逻辑影响分组.然后，您还需要来自与分组中的最大/最小或第一条/最后一条记录相关的行的其他元数据.

在真正只能通过子查询实现的SQL中，无论您使用CTE还是视图或表值变量，执行都是相同的，我们需要强制引擎在应用额外的过滤条件之前判断一个结果集.