mysql - 适合 where 子句的组中的最大值

Question

是的，这是另一个每组最大的问题之一!我已经尝试了几天，试图解决这个问题，但无济于事。我也一直在寻找，但我什至不知道我是否在正确的地方寻找。问题的最简化版本如下。

我有 2 个表，一个是多对多表，另一个有数据。

--------a-------    ------------b-----------
| id | version |    | id  | version | external_ref |
|  1 |    1    |    |  1  |    1    |       9      |
|  1 |    2    |    |  1  |    2    |       8      |
|  1 |    3    |    |  1  |    3    |       7      |
|  2 |    1    |    |  2  |    1    |       5      |
|  2 |    2    |    |  2  |    2    |       6      |
       .                          .
       .                          .
       .                          .

这是我当前(大大简化的)查询以获取结果:

SELECT * FROM a 
    INNER JOIN (
        SELECT MAX(Version) FROM a GROUP BY id
    ) j ON a.id = j.id AND a.version = j.version
    LEFT JOIN b ON a.id = b.id AND a.version = b.version
WHERE  (b.external_ref = 9 
    OR b.external_ref = 8 (ect)
 ) AND (a.id = 1 OR
       a.id = 2)

此查询的问题在于，如果 external_ref = 7 不在 where 子句中，则不会返回任何行。

如果我正在查看只有 external_ref 为 8、9 或 5 的条目，那么我希望能够获得以下结果

| id | version |   data from table a    |
|  1 |    2    | some data from table a |
|  2 |    1    |   some more data       |

那么如何获取满足where子句的MAX(version)呢？

完整的查询是:

SELECT DISTINCT `t`.`Version`, `t`.`TermID`, `t`.`Definition`, `t`.`Name` 
FROM `term` AS `t`
  INNER JOIN (
    SELECT MAX(`Version`) version, `TermID`
    FROM `term`
    GROUP BY `termID`
) jj ON `t`.`TermID` = jj.`TermID` AND `t`.`Version` = jj.`Version`
LEFT JOIN `syllabusin` AS `si` ON `t`.`TermID` = `si`.`TermID` AND `t`.`Version` = `si`.`Version`
LEFT JOIN `synonyms` AS `sy` ON `t`.`TermID` = `sy`.`TermID`
WHERE
  (`si`.`SyllabusID` = 7  
   OR  `si`.`SyllabusID` = 6  
   OR  `si`.`SyllabusID` = 5  
   OR  `si`.`SyllabusID` = 4  
   OR  `si`.`SyllabusID` = 3  
   OR  `si`.`SyllabusID` = 2  
   OR  `si`.`SyllabusID` = 1  
   OR  `si`.`SyllabusID` = 8 )
AND (  `t`.`Name` LIKE '%term%'  
     OR  `t`.`Acronym` LIKE '%term%'  
     OR  `t`.`Definition` LIKE '%term%'  
     OR  `sy`.`Synonym` LIKE '%term%'  )    

编辑:

为了阐明我想要什么，我想从表 a 中获取行，这些行被外部引用(具有 external_ref = x)，其 id 具有最大版本.

Answer 1

我想可能是这样:

SELECT a.*
FROM a
JOIN (
    SELECT a.id, MAX(a.version) AS maxversion
    FROM a
    JOIN b ON a.id = b.id and a.version = b.version
    WHERE b.external_ref IN (9, 8)
    GROUP BY a.id
) AS amax ON a.id = amax.id AND a.version = amax.version

也可以这样写:

SELECT a.*
FROM a
JOIN (
    SELECT a.id, MAX(a.version) AS maxversion
    FROM a
    JOIN (SELECT *
          FROM b
          WHERE external_ref IN (9, 8)) AS b
    ON a.id = b.id and a.version = b.version
    GROUP BY a.id
) AS amax ON a.id = amax.id AND a.version = amax.version

对于更完整的查询，我认为您只需将所有连接和条件移动到子查询中:

SELECT DISTINCT `t`.`Version`, `t`.`TermID`, `t`.`Definition`, `t`.`Name` 
FROM `term` AS `t`
INNER JOIN (
    SELECT MAX(`Version`) version, `TermID`
    FROM `term` AS t
    JOIN syllabusin AS si ON `t`.`TermID` = jj.`TermID` AND `t`.`Version` = jj.`Version`
    JOIN `synonyms` AS `sy` ON `t`.`TermID` = `sy`.`TermID`
    WHERE
      (`si`.`SyllabusID` IN (7, 6, 5, 4, 3, 2, 1, 8)
    AND (  `t`.`Name` LIKE '%term%'  
         OR  `t`.`Acronym` LIKE '%term%'  
         OR  `t`.`Definition` LIKE '%term%'  
         OR  `sy`.`Synonym` LIKE '%term%'  )        
    GROUP BY `termID`
) jj ON `t`.`TermID` = jj.`TermID` AND `t`.`Version` = jj.`Version`

它的逻辑与之前的查询基本相同:您执行包含所有条件的连接，并从中获取每个 ID 的最大版本。然后加入原始表，仅选择与 ID 和最大版本匹配的行。