mysql - 如何查询一个字段与另一个字段相关的最大值？分组依据不起作用？ (MySQL)

Question

我有一个包含 2 列的表:EntryID 及其版本。每个条目可以有多个不同的版本，最高版本是该条目的最新版本。此外，每个entryID 可能有超过1 个名称

EntryID - Version - EntryName212 - 1 - Car212 - 1 - Car2212 - 2 - Batr212 - 2 - hoo451 - 2 - Csert451 - 3 - xxx451 - 3 - xxx2111 - 1 - yyy333 - 4 - ggg

Now, based on the entryID provided, I have a need to get the all the entries that have max versions only.

Ex, the user may enter 212 & 451 & hit the button then it will show:

EntryID - Version - EntryName212 - 2 - Batr212 - 2 - hoo451 - 3 - xxx451 - 3 - xxx2

The below query using group by but doesn't work.

 select * from table where entryID in (212,451) and version in 
(select max(version) from table where entryID in (212,451) group by entryID)

结果:

EntryID - Version - EntryName212 - 2 - Batr212 - 2 - hoo451 - 2 - Csert451 - 3 - xxx451 - 3 - xxx2

这是不正确的，因为条目 451 包含版本 2，这是条目 212 的最大版本。

Answer 1

SELECT t1.*
FROM Table t1
JOIN (SELECT EntryID, MAX(version) maxversion
      FROM Table
      WHERE EntryID IN (212, 451)
      GROUP BY EntryID) t2
ON t1.EntryID = t2.EntryID AND t1.version = t2.maxversion