mysql+id顺序,mysql – 在特定id之后选择具有顺序id的所有行

是否有可能在特定ID之后查询其ID是连续的所有行？ (id是AUTO_INCREMENT列.)

例如,假设我的特定id是5.然后我想要所有带有这些id的行：

6,7,8,9 ……直到这个序列存在.例如.如果没有id = 12的行,那么序列必须在11处停止.所以期望的结果将是具有这些id的行：

6,7,8,9,10,11.

编辑：

一个例子：

这是我的表：

mysql> SELECT * FROM Inheritance ORDER BY id ASC;

+-------------+-------------------+------------+-------------+-------+

| classNS | class | super | superNS | id |

+-------------+-------------------+------------+-------------+-------+

| | Data | Object | | 00002 |

| | Thing | Object | | 00003 |

| BUILD | DATA | Data | | 00004 |

| | Dialable | Data | | 00005 |

| | Phone | Data | | 00006 |

| | Email | Data | | 00007 |

| | Place | Data | | 00008 |

| | Website | Data | | 00009 |

| | SocialNetworkLink | Data | | 00011 |

| | Password | Data | | 00012 |

| | User | Thing | | 00013 |

| | Asset | Thing | | 00014 |

| | Collection | Asset | | 00015 |

| | Product | Asset | | 00016 |

| collections | School | Collection | | 00017 |

| collections | LangSchool | Collection | | 00018 |

| BUILD | OWNER | DATA | BUILD | 00022 |

| BUILD | OWNER_OF_PRODUCT | DATA | BUILD | 00023 |

| BUILD | OWNER_OF_EMAIL | DATA | BUILD | 00024 |

| BUILD | COUNTRY | DATA | BUILD | 00025 |

| BUILD | SOCIAL_NETWORK | DATA | BUILD | 00026 |

| BUILD | PROVINCE_IR | DATA | BUILD | 00027 |

| collections | SchoolPlus | School | collections | 00028 |

+-------------+-------------------+------------+-------------+-------+

23 rows in set (0.00 sec)

然后我想要这个结果(当我的特定id为5时)：

+---------+----------+-------+---------+-------+

| classNS | class | super | superNS | id |

+---------+----------+-------+---------+-------+

| | Phone | Data | | 00006 |

| | Email | Data | | 00007 |

| | Place | Data | | 00008 |

| | Website | Data | | 00009 |

+---------+----------+-------+---------+-------+

请注意,源表中缺少id = 10的行.

解决方法:

不使用递归CTE或窗口函数或变量的查询：

SELECT i.*

FROM Inheritance AS i

JOIN

( SELECT ui.id

FROM Inheritance AS ui

WHERE ui.id > 5

AND NOT EXISTS

( SELECT 1

FROM Inheritance AS pi

WHERE pi.id = ui.id - 1

)

ORDER BY ui.id

LIMIT 1

) AS up

ON i.id < up.id

WHERE i.id > 5 ;

标签：mysql,select,sequence

来源： https://codeday.me/bug/20190807/1606051.html