sql – 在两个IP范围之间选择记录

1,Lucas,804645,192.130.1.1,192.130.1.254
2,Maria,222255,192.168.2.1,192.168.2.254
3,Julia,123456,192.150.3.1,192.150.3.254

现在,如果我有一个IP地址192.168.2.50,我如何检索匹配的记录？

编辑

基于Gordon的答案(我正在收到编译错误)这是我所拥有的：

select PersonnelPC.*
from (select PersonnelPC.*,(
              cast(parsename(iplow,4)*1000000000 as decimal(12,0)) +
              cast(parsename(iplow,3)*1000000 as decimal(12,2)*1000 as decimal(12,0)) +
              (parsename(iplow,1))
             ) as iplow_decimal,(
              cast(parsename(iphigh,0)) +
              cast(parsename(iphigh,0)) +
              (parsename(iphigh,1))
             ) as iphigh_decimal
      from PersonnelPC
     ) PersonnelPC
where 192168002050 between iplow_decimal and iphigh_decimal;

但这给我一个错误：

Msg 8115,Level 16,State 2,Line 1
Arithmetic overflow error converting expression to data type int.

有任何想法吗？

解决方法

select t.*
from (select t.*,(cast(parsename(iplow,4)*1000000000.0 as decimal(12,3)*1000000.0 as decimal(12,2)*1000.0 as decimal(12,1) as decimal(12,0))
             ) as iplow_decimal,(cast(parsename(iphigh,0))
             ) as iphigh_decimal
      from t
     ) t
where 192168002050 between iplow_decimal and iphigh_decimal;

我应该注意,IP地址通常作为4字节的无符号整数存储在数据库中.这使得比较更容易. . .虽然您需要复杂的逻辑(通常包装在一个函数中)来将值转换为可读格式.

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!