MySQL - 获取缺失的 2 列 ID

Question

我有一个这样的表:

  Code | ID | AAA | 1  | AAA | 2  | AAA | 4  | AAA | 5  | BBB | 1  | BBB | 4  | BBB | 5  | BBB | 6  |

仅使用选择查询可以列出缺少的“code”和“id”吗？

  Code | ID | AAA | 3  | BBB | 2  | BBB | 3  |

我在下面的链接中找到了一个半途解决方案代码(仅适用于一列): MySQL get missing IDs from table任何帮助将不胜感激。

Answer 1

非常相似，只需在 where 子句和 group by 子句中包含另一列即可:

SELECT a.id+1 AS start, MIN(b.id) - 1 AS end
    FROM testtable AS a, testtable AS b
    WHERE a.id < b.id AND a.code = b.code
    GROUP BY a.code, a.id
    HAVING start < MIN(b.id)