Home >Database >Mysql Tutorial >mysql查找不存在的id_MySQL

mysql查找不存在的id_MySQL

WBOY
WBOYOriginal
2016-06-02 08:49:502218browse

最近在群里有人问到怎样才能将mysql表中 查找不存在的id(id自增,或者连续都可以)

第一种方法:

select bewin_id,a from

(
select bewin_id,1 as a from (select bewin_id from c_userinfo_his order by bewin_id asc) t where not exists (select 1 from c_userinfo_his where bewin_id=t.bewin_id-1)
union
select bewin_id,2 as a from (select bewin_id from c_userinfo_his order by bewin_id asc) t where not exists (select 1 from c_userinfo_his where bewin_id=t.bewin_id+1)

) t order by bewin_id

解释下select bewin_id,1 as a from (select bewin_id from c_userinfo_his order by bewin_id asc) t where not exists (select 1 from c_userinfo_his where bewin_id=t.bewin_id-1)

这个sql查到的id都是缺少这个id的前一个id(有点拗口,举例就是查到的id为7则6是不存在这张表里的)

select bewin_id,2 as a from (select bewin_id from c_userinfo_his order by bewin_id asc) t where not exists (select 1 from c_userinfo_his where bewin_id=t.bewin_id+1)

这个sql查到的id都是缺少这个id的后一个id(举例就是查到的id为7则8是不存在这张表里的)

所以看结果\

就可以直接知道456和1213等等都是不存在的,上面的有个缺点就是要自己去数

第二种方法

创建一张表(c_userinfo_his_test)只有id和另一个字段test,并且id是连续的,最大的id为select max(bewin_id) from c_userinfo_his,这样得到一张完整的id(insert into c_userinfo_his_test(test) values ('1')使bewin_id等于max(bewin_id)),

然后就是执行select bewin_id from c_userinfo_his_tset where bewin_id not in (select bewin_id from c_userinfo_his)

这个语句就可以得到想要的结果了(这里test写错了大笑

如图\

和前面是一样的。

Statement:
The content of this article is voluntarily contributed by netizens, and the copyright belongs to the original author. This site does not assume corresponding legal responsibility. If you find any content suspected of plagiarism or infringement, please contact admin@php.cn