如何在mysql中查找顺序编号的差距？
如何在mysql中查找顺序编号的差距？
提问于 2010-12-03 06:45:56
我们有一个数据库，其中有一个表，其值是从另一个系统导入的。有一个自动增量列，没有重复的值，但有缺失值。例如，运行以下查询：
select count(id) from arrc_vouchers where id between 1 and 100应该返回100，但它返回87。有没有我可以运行的查询来返回缺少的数字的值？例如，可能存在id为1-70和83-100的记录，但不存在id为71-82的记录。我想返回71，72，73，等等。
这个是可能的吗？
回答 9
Stack Overflow用户
发布于 2015-04-20 06:32:31
这对我在一个超过80k行的表中找到差距很有效：
SELECT
CONCAT(z.expected, IF(z.got-1>z.expected, CONCAT(' thru ',z.got-1), '')) AS missing
FROM (
SELECT
@rownum:=@rownum+1 AS expected,
IF(@rownum=YourCol, 0, @rownum:=YourCol) AS got
FROM
(SELECT @rownum:=0) AS a
JOIN YourTable
ORDER BY YourCol
) AS z
WHERE z.got!=0;结果：
+------------------+
| missing |
+------------------+
| 1 thru 99 |
| 666 thru 667 |
| 50000 |
| 66419 thru 66456 |
+------------------+
4 rows in set (0.06 sec)请注意，列expected和got的顺序非常重要。
如果您知道YourCol不是从1开始的，这并不重要，您可以替换
(SELECT @rownum:=0) AS a使用
(SELECT @rownum:=(SELECT MIN(YourCol)-1 FROM YourTable)) AS a新结果：
+------------------+
| missing |
+------------------+
| 666 thru 667 |
| 50000 |
| 66419 thru 66456 |
+------------------+
3 rows in set (0.06 sec)如果需要对缺少的If执行某种shell脚本任务，还可以使用此变体直接生成可以在bash中迭代的表达式。
SELECT GROUP_CONCAT(IF(z.got-1>z.expected, CONCAT('$(',z.expected,' ',z.got-1,')'), z.expected) SEPARATOR " ") AS missing
FROM ( SELECT @rownum:=@rownum+1 AS expected, IF(@rownum=height, 0, @rownum:=height) AS got FROM (SELECT @rownum:=0) AS a JOIN block ORDER BY height ) AS z WHERE z.got!=0;这将产生如下输出
$(seq 1 99) $(seq 666 667) 50000 $(seq 66419 66456)然后，可以将其复制并粘贴到bash终端的for循环中，以便为每个ID执行一个命令
for ID in $(seq 1 99) $(seq 666 667) 50000 $(seq 66419 66456); do
echo $ID
# fill the gaps
done它和上面的一样，只是它既是可读的，也是可执行的。通过更改上面的"CONCAT“命令，可以为其他编程语言生成语法。或者甚至是SQL。
Stack Overflow用户
发布于 2017-01-10 22:33:09
如果您使用的是MariaDB，则可以使用sequence storage engine更快(800%)的选项
SELECT * FROM seq_1_to_50000 WHERE SEQ NOT IN (SELECT COL FROM TABLE);Stack Overflow用户
发布于 2010-12-03 06:54:35
创建一个包含100行和一列的临时表，其中包含值1-100。
将此表外部连接到您的arrc_vouchers表，并选择arrc_vouchers id为null的单个列值。
对此进行盲目编码，但应该可以工作。
select tempid from temptable
left join arrc_vouchers on temptable.tempid = arrc_vouchers.id
where arrc_vouchers.id is null页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://stackoverflow.com/questions/4340793
复制相关文章
相似问题