表中有个存放IP地址的段落,存放格式:
ID IP
1 68.1.1.1-68.99.99.99,200.11.11.11-200.99.99.99
2 68.99.99.99-68.99.99.99,200.11.11.11-200.99.99.99比如我传一个IP(如68.1.1.254,应该在ID为1所限定的IP段内),判断这个IP是否在ID为1所限定的IP中,怎么判断。
ID为1的IP中数据表示IP限制为68.1.1.1到68.99.99.99和200.11.11.11到200.99.99.99如果这样不好判断的话,我写入IP时也可以改成068001001001-068099099099,200011011011-200099099099这样加“0”写入
然后判断068001001254这个串是否在上个字符串中。帮忙,谢谢。
ID IP
1 68.1.1.1-68.99.99.99,200.11.11.11-200.99.99.99
2 68.99.99.99-68.99.99.99,200.11.11.11-200.99.99.99比如我传一个IP(如68.1.1.254,应该在ID为1所限定的IP段内),判断这个IP是否在ID为1所限定的IP中,怎么判断。
ID为1的IP中数据表示IP限制为68.1.1.1到68.99.99.99和200.11.11.11到200.99.99.99如果这样不好判断的话,我写入IP时也可以改成068001001001-068099099099,200011011011-200099099099这样加“0”写入
然后判断068001001254这个串是否在上个字符串中。帮忙,谢谢。
WITH IP_TABLE AS
(
SELECT 1 AS ID,'068001001001-068099099099,200011011011-200099099099' AS IP FROM DUAL
UNION
SELECT 2 AS ID,'068099099099-068099099099,200011011011-200099099099' AS IP FROM DUAL
) SELECT R.ID,R.IP
FROM
(
SELECT T.*,
SUBSTR((SUBSTR(T.IP,
1,
INSTR(T.IP,
',') - 1)),
1,
INSTR((SUBSTR(T.IP,
1,
INSTR(T.IP,
',') - 1)),
'-') - 1) AS IP_1,
SUBSTR((SUBSTR(T.IP,
1,
INSTR(T.IP,
',') - 1)),
INSTR((SUBSTR(T.IP,
1,
INSTR(T.IP,
',') - 1)),
'-') + 1) AS IP_2,
SUBSTR((SUBSTR(T.IP,
INSTR(T.IP,
',') + 1)),
1,
INSTR((SUBSTR(T.IP,
INSTR(T.IP,
',') + 1)),
'-') - 1) AS IP_3,
SUBSTR((SUBSTR(T.IP,
INSTR(T.IP,
',') + 1)),
INSTR((SUBSTR(T.IP,
INSTR(T.IP,
',') + 1)),
'-') + 1) AS IP_4
FROM IP_TABLE T
) R
WHERE ('068001001254' > R.IP_1 AND '068001001254' <= R.IP_2)
OR ('068001001254' > R.IP_3 AND '068001001254' <= R.IP_4)
ID IP
1 068001001001-068099099099,200011011011-200099099099
068001001001-068099099099,200011011011-200099099099
SELECT ID,IP FROM
WHERE SUBSTR(IP,0,INSTR(IP,'-')-1)<='068001001254'
AND SUBSTR(IP,INSTR(IP,'-')+1)>='068001001254'
如果有多个ip段的话,就只能用动态sql实现了。
-----------------------------------------------------
能不能存成这样?这样好处理多了
1 068001001001-068099099099
1 200.11.11.11-200099099099
2 068099099099-068099099099
2 200011011011-200099099099 如果按照这种情况,那就简单多了。
create or replace function find_ip(p_ip in varchar2) return number
is
i number;
v_id number;
v_id1 number;
v_str varchar2(1000);
cursor v_cur is
select id,p_ip||' between '||replace(replace(IP,'-',' and '),',',' or '||p_ip||' between ') from table order by id;
begin
open v_cur;
fetch v_cur into v_id,v_str;
while v_cur %found loop
execute immediate 'select count(1) from table where '||v_str into i;
if i>0 then
execute immediate 'select id from table where '||v_str into v_id1;
end if;
fetch v_cur into v_id,v_str;
end loop;
close v_cur;
return 0;
end;
/
create or replace function find_ip(p_ip in varchar2) return number
is
i number;
v_id number;
v_str varchar2(1000);
cursor v_cur is
select id,p_ip||' between '||replace(replace(IP,'-',' and '),',',' or '||p_ip||' between ') from table order by id;
begin
open v_cur;
fetch v_cur into v_id,v_str;
while v_cur %found loop
execute immediate 'select count(1) from table where '||v_str into i;
if i>0 then
return v_id;
end if;
fetch v_cur into v_id,v_str;
end loop;
close v_cur;
return 0;
end;
/
A IP
B 68.1.1.1-68.99.99.99,200.11.11.11-200.99.99.99
with a as
(
SELECT rownum r,
SUBSTR(',' || B || ',',
INSTR(',' || B || ',', ',', 1, RN) + 1,
INSTR(',' || B || ',', ',', 1, RN + 1) -
INSTR(',' || B || ',', ',', 1, RN) - 1) B
FROM TEST_ABC,
(SELECT ROWNUM RN
FROM ALL_OBJECTS
WHERE ROWNUM <= (SELECT MAX(LENGTH(B) - LENGTH(REPLACE(B, ',', '')))
FROM TEST_ABC) + 1) OB
WHERE INSTR(B || ',', ',', 1, RN) > 0
)
select min(i1),min(i1),min(i2) ,max(i2),min(i3) ,max(i3),min(i4) ,max(i4)
from(
select r,
substr(b1,0,3) i1,
substr(b1,4,3) i2,
substr(b1,7,3) i3,
substr(b1,10,3) i4
from(
SELECT SUBSTR('-' || B || '-',
INSTR('-' || B || '-', '-', 1, RN) + 1,
INSTR('-' || B || '-', '-', 1, RN + 1) -
INSTR('-' || B || '-', '-', 1, RN) - 1) B1,r
FROM a,
(SELECT ROWNUM RN
FROM ALL_OBJECTS
WHERE ROWNUM <= (SELECT MAX(LENGTH(B) - LENGTH(REPLACE(B, '-', '')))
FROM a) + 1) OB
WHERE INSTR(B || '-', '-', 1, RN) > 0
)
)
group by r
是这样的
TEST_ABC
A 1
B 068001001001-068099099099,200011011011-200099099099'
有点太复杂化。SQL语句上没有啥可以建议的。数据不是很多的情况,你就按照superhsj 说的来做,更简单些。