mysql - 如何在mySQL上找到不规则的增加

Question

我的表:

id         value
1          25
2          96
5          47
6          41
9          78
10         23

如何找到不规则的增加(或不存在的行)，如下所示:

结果:3、4、7、8

我不想逐一阅读，因为有 50k 行。你建议我做什么？

Answer 1

简单方法

成分:

• 生成器表
• 外连接

SQL Fiddle

MySQL 5.6 架构设置:

create table t ( id int, value int );
insert into t values ( 1, 12 );
insert into t values ( 3, 12 );
insert into t values ( 7, 12 );
insert into t values ( 9, 12 );

查询 1:

select generator.row
from t right outer join
( 
  SELECT @row := @row + 1 as row FROM 
  (select 0 union all select 1 union all select 3 union all select 4 union all select 5 union all select 6 union all select 6 union all select 7 union all select 8 union all select 9) t,
  (select 0 union all select 1 union all select 3 union all select 4 union all select 5 union all select 6 union all select 6 union all select 7 union all select 8 union all select 9) t2, 
  (select 0 union all select 1 union all select 3 union all select 4 union all select 5 union all select 6 union all select 6 union all select 7 union all select 8 union all select 9) t3, 
  (select 0 union all select 1 union all select 3 union all select 4 union all select 5 union all select 6 union all select 6 union all select 7 union all select 8 union all select 9) t4, 
  (SELECT @row:=0) t5
) generator
on generator.row = t.id
where t.id is null and generator.row < ( select max( t.id) from t )

Results :

| row |
|-----|
|   2 |
|   4 |
|   5 |
|   6 |
|   8 |

复杂的方法

如果你需要间隙限制，你需要一些成分吗:

• MySQL 变量
• > Generator table至少有你的最大ID

Here your query

MySQL 5.6 架构设置:

create table t ( id int, value int );
insert into t values ( 1, 12 );
insert into t values ( 3, 12 );
insert into t values ( 7, 12 );
insert into t values ( 9, 12 );

查询:

select generator.row, rFrom, rTo
from (
  SELECT @r := @r + 1 as r, 
         case 
            when @r < t.id then @r
            else Null
         end as rFrom,
         case 
            when @r < t.id then t.id - 1
            else Null
         end as rTo,
         @r := t.id 
  FROM t , (SELECT @r:=0) r
) gaps inner join
( 
  SELECT @row := @row + 1 as row FROM 
  (select 0 union all select 1 union all select 3 union all select 4 union all select 5 union all select 6 union all select 6 union all select 7 union all select 8 union all select 9) t,
  (select 0 union all select 1 union all select 3 union all select 4 union all select 5 union all select 6 union all select 6 union all select 7 union all select 8 union all select 9) t2, 
  (select 0 union all select 1 union all select 3 union all select 4 union all select 5 union all select 6 union all select 6 union all select 7 union all select 8 union all select 9) t3, 
  (select 0 union all select 1 union all select 3 union all select 4 union all select 5 union all select 6 union all select 6 union all select 7 union all select 8 union all select 9) t4, 
  (SELECT @row:=0) t5
) generator
on generator.row between gaps.rFrom and gaps.rTo
where rFrom is not null

Results :

| row | rFrom | rTo |
|-----|-------|-----|
|   2 |     2 |   2 |
|   4 |     4 |   6 |
|   5 |     4 |   6 |
|   6 |     4 |   6 |
|   8 |     8 |   8 |

第一个子查询查找间隙间隔，第二个子查询是生成缺失 ID 的生成器。可以自由地一个一个地执行这两个查询以理解它。