mysql - 选择中间不存在的ID

Question

SELECT ID FROM TABLE WHERE ID BETWEEN 1 AND 5

上面查询的结果将是:1,2,3,4,5

如果只有 id 1和 id 2谁存在于表中，那么它将循环(1,2)只要。

这意味着，如果 id介于 1 AND 5 之间, id的 3,4,5不存在。

id的 3,4,5是我要选择的。这该怎么做？

提前致谢。

Answer 1

试试这个:

select * from 
(
   select 1 as val
   union all
   select 2
   union all
   select 3 
   union all
   select 4
   union all
   select 5
)t
left join TableName tn on t.val = tn.id
where tn.id is null

使用计数表:

select * from (
select (3 + th*1000+h*100+t*10+u+1) x from
(select 0 th union select 1 union select 2 union select 3 union select 4 union
select 5 union select 6 union select 7 union select 8 union select 9) A,
(select 0 h union select 1 union select 2 union select 3 union select 4 union
select 5 union select 6 union select 7 union select 8 union select 9) B,
(select 0 t union select 1 union select 2 union select 3 union select 4 union
select 5 union select 6 union select 7 union select 8 union select 9) C,
(select 0 u union select 1 union select 2 union select 3 union select 4 union
select 5 union select 6 union select 7 union select 8 union select 9) D)
tn
left join t on tn.x = t.id
where tn.x between 3 and 10
and t.id is null

注意公式 (3 + th*1000+h*100+t*10+u+1) 和 where clause 3 is srart, 10 是 end。改变变量。

这是 fiddle :http://sqlfiddle.com/#!9/2f53f/2