举例:
区号 begin_ip end_ip
1 25 202.138.192.0 202.138.207.255
2 25 202.139.96.0 202.139.111.255
3 513 202.139.112.0 202.139.122.255
4 527 202.139.123.0 202.139.127.255
5 515 202.103.96.0 202.103.103.255 (假设的)这个表A为对应的ip段对应的地区现有B表,里面是ip地址,如:211.139.119.123(假设的)
要查询ip对应的区号,怎么实现呢??
注意!!!begin和end的不一定是0-255,有的可能很特殊!我前三段用的查找脚本是:where substr('ip', 1, instr('ip', '.', 1)) between
substr(begin_ip, 1, instr(begin_ip, '.', 1)) and
substr(end_ip, 1, instr(end_ip, '.', 1))
and substr('ip',
instr('ip', '.', 1),
instr('ip', '.', 1, 2)) between
substr(begin_ip, instr(begin_ip, '.', 1), instr(begin_ip, '.', 1, 2)) and
substr(end_ip, instr(end_ip, '.', 1), instr(end_ip, '.', 1, 2))
and substr('ip',
instr('ip', '.', 1, 2),
instr('ip', '.', 1, 3)) between
substr(begin_ip,
instr(begin_ip, '.', 1, 2),
instr(begin_ip, '.', 1, 3)) and
substr(end_ip, instr(end_ip, '.', 1, 2), instr(end_ip, '.', 1, 3))请问第四段怎么写呢?或者有更好的办法呢?
区号 begin_ip end_ip
1 25 202.138.192.0 202.138.207.255
2 25 202.139.96.0 202.139.111.255
3 513 202.139.112.0 202.139.122.255
4 527 202.139.123.0 202.139.127.255
5 515 202.103.96.0 202.103.103.255 (假设的)这个表A为对应的ip段对应的地区现有B表,里面是ip地址,如:211.139.119.123(假设的)
要查询ip对应的区号,怎么实现呢??
注意!!!begin和end的不一定是0-255,有的可能很特殊!我前三段用的查找脚本是:where substr('ip', 1, instr('ip', '.', 1)) between
substr(begin_ip, 1, instr(begin_ip, '.', 1)) and
substr(end_ip, 1, instr(end_ip, '.', 1))
and substr('ip',
instr('ip', '.', 1),
instr('ip', '.', 1, 2)) between
substr(begin_ip, instr(begin_ip, '.', 1), instr(begin_ip, '.', 1, 2)) and
substr(end_ip, instr(end_ip, '.', 1), instr(end_ip, '.', 1, 2))
and substr('ip',
instr('ip', '.', 1, 2),
instr('ip', '.', 1, 3)) between
substr(begin_ip,
instr(begin_ip, '.', 1, 2),
instr(begin_ip, '.', 1, 3)) and
substr(end_ip, instr(end_ip, '.', 1, 2), instr(end_ip, '.', 1, 3))请问第四段怎么写呢?或者有更好的办法呢?
然后再转换成10进制就是3546767360
然后再转换end_ip
这样出来的十进制的区间是个连续的区间
然后再把要查的IP也转换下,就能查了,不用考虑第几段的问题的还有种办法就是A表的结构保证BEGIN和END的前三段都一样,即纪录增多