MySQL从表中丢失ID [英] MySQL get missing IDs from table

问题描述

我在MySQL中有此表，例如:

I have this table in MySQL, for example:

ID | Name
1  | Bob
4  | Adam
6  | Someguy

如果您注意到，则没有ID号(2、3和5).

If you notice, there is no ID number (2, 3 and 5).

如何编写查询，以便MySQL仅回答缺少的ID，在这种情况下为"2,3,5"?

How can I write a query so that MySQL would answer the missing IDs only, in this case: "2,3,5" ?

推荐答案

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

希望此链接也有帮助 http://www.codediesel.com/mysql/sequence-gaps-in-mysql/

Hope this link also helps http://www.codediesel.com/mysql/sequence-gaps-in-mysql/

这篇关于MySQL从表中丢失ID的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！