请问大家:比如我有一个IP数据表。如下:
[ip1] [ip2] [city]
219.139.113.74 219.139.113.74 湖北省咸宁市 通山县新起点网吧
219.139.113.75 219.139.113.101 湖北省咸宁市 电信
219.139.113.102 219.139.113.102 湖北省咸宁市 通山县金福利网吧
219.139.113.103 219.139.113.109 湖北省咸宁市 (通城县)电信
219.139.113.110 219.139.113.110 湖北省咸宁市 通山县一通网吧
219.139.113.111 219.139.113.255 湖北省咸宁市 (通城县)电信
219.139.114.0 219.139.114.5 湖北省咸宁市 崇阳县电信我想求出:219.139.113.121这个IP属于哪个city,请问用一句SQL应该怎么写?
[ip1] [ip2] [city]
219.139.113.74 219.139.113.74 湖北省咸宁市 通山县新起点网吧
219.139.113.75 219.139.113.101 湖北省咸宁市 电信
219.139.113.102 219.139.113.102 湖北省咸宁市 通山县金福利网吧
219.139.113.103 219.139.113.109 湖北省咸宁市 (通城县)电信
219.139.113.110 219.139.113.110 湖北省咸宁市 通山县一通网吧
219.139.113.111 219.139.113.255 湖北省咸宁市 (通城县)电信
219.139.114.0 219.139.114.5 湖北省咸宁市 崇阳县电信我想求出:219.139.113.121这个IP属于哪个city,请问用一句SQL应该怎么写?
把IP格式化为三位,然后比较,这是山寨版的具体的就不写了
insert into tb values('219.139.113.74' ,'219.139.113.74' ,'湖北省咸宁市 通山县新起点网吧')
insert into tb values('219.139.113.75' ,'219.139.113.101' ,'湖北省咸宁市 电信')
insert into tb values('219.139.113.102' ,'219.139.113.102' ,'湖北省咸宁市 通山县金福利网吧')
insert into tb values('219.139.113.103' ,'219.139.113.109' ,'湖北省咸宁市 (通城县)电信')
insert into tb values('219.139.113.110' ,'219.139.113.110' ,'湖北省咸宁市 通山县一通网吧')
insert into tb values('219.139.113.111' ,'219.139.113.255' ,'湖北省咸宁市 (通城县)电信')
insert into tb values('219.139.114.0' ,'219.139.114.5' ,'湖北省咸宁市 崇阳县电信')
godeclare @IP as varchar(15)
set @IP = '219.139.113.121'select * from tb where
right('000'+parsename(@IP,4),3) + right('000'+parsename(@IP,3),3) + right('000'+parsename(@IP,2),3) + right('000'+parsename(@IP,1),3)
between
right('000'+parsename(ip1,4),3) + right('000'+parsename(ip1,3),3) + right('000'+parsename(ip1,2),3) + right('000'+parsename(ip1,1),3)
and
right('000'+parsename(ip2,4),3) + right('000'+parsename(ip2,3),3) + right('000'+parsename(ip2,2),3) + right('000'+parsename(ip2,1),3)drop table tb/*
ip1 ip2 city
--------------- --------------- --------------------------------------------------
219.139.113.111 219.139.113.255 湖北省咸宁市 (通城县)电信(所影响的行数为 1 行)
*/