我有一个包含列id和name的表。我的重点是id的订单。事实上，我想选择行，当数字的id顺序中断时。看看我的例子：
// mytable
+----+-----------+
| id | name |
+----+-----------+
| 1 | ali |
| 2 | jack |
| 3 | peter |
| 5 | steve |
| 6 | lenord |
| 7 | jack |
| 9 | fered |
+----+-----------+
现在我想要select where id=5和select where id=9。因为CCD_ 7和CCD_。
编辑：我想要这个输出：
// mytable
+----+-----------+
| id | name |
+----+-----------+
| 5 | steve |
| 9 | fered |
+----+-----------+
我可以那样做吗？
事实上，您想要id仅在缺少的id之上的记录；因此，您需要搜索具有id的记录，以使id-1不存在（对于最低id，情况总是如此，因此我们必须明确排除id=1）
SELECT *
FROM the_table tt
WHERE id > 1
AND NOT EXISTS (
SELECT *
FROM the_table nx
WHERE nx.id = tt.id -1
);
尝试使用特定ID进行查找。http://php.net/manual/en/mysqli-result.data-seek.php
<?php
/* Open a connection */
$mysqli = new mysqli("localhost", "my_user", "my_password", "world");
/* check connection */
if (mysqli_connect_errno()) {
printf("Connect failed: %s'n", mysqli_connect_error());
exit();
}
$query = "SELECT id, name FROM table";
if ($result = $mysqli->query($query)) {
/* seek to row no. 4 */
$result->data_seek(3);
/* fetch row */
$row = $result->fetch_row();
printf ("ID: %s Name: %s'n", $row[0], $row[1]);
/* seek to row no. 7 */
$result->data_seek(6);
/* fetch row */
$row = $result->fetch_row();
printf ("ID: %s Name: %s'n", $row[0], $row[1]);
/* free result set*/
$result->close();
}
/* close connection */
$mysqli->close();
?>
这应该返回：
ID: 5 Name: steve
ID: 9 Name: fered
SELECT x.*
FROM my_table x
LEFT
JOIN my_table y
ON y.id = x.id - 1
WHERE y.id IS NULL
AND x.id > 1;
+----+-------+
| id | name |
+----+-------+
| 5 | steve |
| 9 | fered |
+----+-------+