mysql 如何显示在一定范围内跳过的数字

Question

我在mysql中查询如下SELECT ont as next_ont_id FROM ont_activation WHERE olt_id ='OLT-601167-1' AND lt = '1'  AND pon = '2' AND activation_status ='Activated';输出：next_ont_id 65 67 68 69 70如何在该输出中获得 66 的值？

Answer 1

您可以使用 PHP 来实现这一点 -

$val = explode(" ", "65 67 68 69 70");

$all = range($val[0], end($val)); // get all values between lowest & largest

print_r(array_diff($all, $val)); // check difference

输出 -

Array

(

    [1] => 66

)

Answer 2

如果你确定这不是抽样错误。也许您应该再次索引数据。在 MySQL 中是这样完成的

set @ROW = 0;

UPDATE `ont` SET `olt_id` = @ROW := @ROW+1 ORDER BY `olt_id` ASC;