如何写这个SQL语句--A表存IP范围，B表存IP,求B表中不在A表的IP范围内的IP
A表
col1                                   col2
10.13.1.1                         10.13.1.100
12.1.1.1                           12.1.1.100
B表
col3
13.1.0.1
10.1.1.1

如何用一个SQL语句取得--B表中不在A表的IP范围内的IP

------解决方案--------------------
CREATE FUNCTION [dbo].[FunCmpIp] (@startip varchar(40))
RETURNS decimal
AS
BEGIN
declare @i int --标记ip地址中的 ". "符号位置
declare @pw int --2进制的幂值
declare @ipvalue decimal--计算ip的二进制值
set @ipvalue=0
set @pw=3
set @i=charindex( '. ',@startip)
while @i> =1
begin
set @ipvalue=@ipvalue+CAST(replace(left(@startip,@i-1), ' ', ' ') as decimal)*POWER(256,@pw)
set @startip=substring(@startip,@i+1,len(@startip)-@i)
set @i=charindex( '. ',@startip)
set @pw=@pw-1
end
set @ipvalue=@ipvalue+CAST(@startip as int)
return @ipvalue
END
go
select col3 from b where (select count(*) from a where dbo.FunCmpIp(b.col3) between dbo.FunCmpIp(a.col1) and dbo.FunCmpIp(a.col2))=0
------解决方案--------------------
create table A
(
col1 nvarchar(20),
col2 nvarchar(20)
)

create table B
(
col1 nvarchar(20)
)

insert into A
select '10.13.1.1 ', '10.13.1.100 ' union all
select '12.1.1.1 ', '12.1.1.100 '

insert into B
select '13.1.0.1 ' union all
select '10.1.1.1 ' union all
select '10.13.1.2 '


select *
from B
where not exists (select *
from A
where reverse(stuff(reverse(B.col1), 1, Patindex( '%.% ', reverse(B.col1)), ' '))
= reverse(stuff(reverse(A.col1), 1, Patindex( '%.% ', reverse(A.col1)), ' '))
and reverse(left(reverse(B.col1), Patindex( '%.% ', reverse(B.col1)) - 1))
between reverse(left(reverse(B.col1), Patindex( '%.% ', reverse(B.col1)) - 1))
and reverse(left(reverse(B.col1), Patindex( '%.% ', reverse(B.col1)) - 1)))

思路：先找到在表A中IP前段与B中相同的IP，然后判断后段是否相等