MySQL在一个表中获取每个版本的最后2个

Question

我想检索进程的 2 个最后版本:

| id | name  | version | souce_id || 1  | test1 | 1.00    | 0        || 2  | test1 | 1.01    | 1        || 3  | test1 | 1.02    | 1        || 4  | test1 | 1.03    | 1        || 5  | test2 | 1.00    | 0        || 6  | test2 | 1.01    | 4        || 7  | test2 | 1.02    | 4        || 8  | test3 | 1.00    | 0        |

预期的结果是:

| id | name  | version | souce_id || 3  | test1 | 1.02    | 1        || 4  | test1 | 1.03    | 1        || 6  | test2 | 1.01    | 4        || 7  | test2 | 1.02    | 4        || 8  | test3 | 1.00    | 0        |

我已经编写了这个查询，但我不喜欢它...而且我不能将它作为子查询包含在 WHERE <code>id</code> IN (...) 中.
SELECT (SELECT SUBSTRING_INDEX(GROUP_CONCAT(<code>id</code> ORDER BY <code>process_version</code> DESC), ',', 2) FROM <code>process</code> WHERE <code>process_name</code> = <code>parent</code>.<code>process_name</code>) AS <code>ids</code> FROM <code>process</code> AS parent WHERE 1 GROUP BY <code>process_name</code>


Answer 1

例如

SELECT x.*
  FROM my_table x
  JOIN my_table y
    ON y.name = x.name
   AND y.id <= x.id
 GROUP
    BY x.id
 HAVING COUNT(*) <=2;