mysql - Select ID which has not been exist in between -
select id table id between 1 , 5
the result of query above be: 1,2,3,4,5
if id
1
, id
2
exist in table, loop (1,2)
only.
it means, if id
between 1 , 5
, id
of 3,4,5
doesn't exist.
the id
of 3,4,5
want select. how this?
thanks in advance.
try this:
select * ( select 1 val union select 2 union select 3 union select 4 union select 5 )t left join tablename tn on t.val = tn.id tn.id null
with tally table
:
select * ( select (3 + th*1000+h*100+t*10+u+1) x (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 tn.x between 3 , 10 , t.id null
notice in formula (3 + th*1000+h*100+t*10+u+1)
, where
clause 3 srart
, 10 end
. change on variables.
here fiddle: http://sqlfiddle.com/#!9/2f53f/2
Comments
Post a Comment