sql 不同ip地址查询

1首先要在不同的机器上建立信任关系
2然后就可以进行不同ip的数据表查询，此处以sqlserver为例：select from 12121212masterdbotable1

用了一个比较笨的方法 暂时没想到很好的方法
select LOCATION from T_Base_IPAddressInfo
where substr('21806182',1,instr('21806182','')-1)
between substr(startip,1,instr(startip,'')-1) and substr(endip,1, instr('20298068','',1,1)-1)
and substr('21806182',instr('20298068','',1,1),instr('21806182','',1,2)-1)
between substr(startip,instr('20298068','',1,1),instr(startip,'',1,1)-1) and substr(endip,instr('20298068','',1,1), instr('20298068','',1,2)-1)
and substr('21806182',instr('20298068','',1,2),instr('21806182','',1,3)-1)
between substr(startip,instr('20298068','',1,2),instr(startip,'',1,2)-1) and substr(endip,instr('20298068','',1,2), instr('20298068','',1,3)-1)
and substr('21806182',instr('20298068','',1,3))
between substr(startip,instr('20298068','',1,3),instr(startip,'',1,3)-1) and substr(endip,instr('20298068','',1,3), instr('20298068','',1,3)-1)

可通过LEFT函数实现。如下实例：

如某数据库中表Table1的IP列，储存了IP地址的信息。因IP地址前两段（共7位）固定不变，可直接用select left(ip,7) from table1 where 1 = 1

笔者亦通过定义临时变量取值的方法，演示如下图：

select
LOCATION
from
T_Base_IPAddressInfo
where
'192168072'
between
startip
and
endip
这样可以查,但是不准确
select
SUBSTR('21806182',
1,length('21806182')
-
instr('',
reverse('21806182')))
from
(select

from
T_Base_IPAddressInfo
where
'21806182'
between
startip
and
endip)
t
where
startip
=
SUBSTR('21806182',
1,length('21806182')
-
instr('',
reverse('21806182')))
||
SUBSTR(startip,
instr('',
reverse(startip)))
需要了解下substr
好instr
的用法

要获取数据库服务器IP，可通过xp_cmdshell 来获取信息，然后对信息进行筛选
xp_cmdshell 扩展存储过程将命令字符串作为 *** 作系统命令 shell 执行，并以文本行的形式返回所有输出。由于存在安全隐患，所以在SQL Server 中， xp_cmdshell 默认是关闭的。
实现代码如下：
--开启xp_cmdshell
exec sp_configure 'show advanced options', 1
reconfigure with override
exec sp_configure 'xp_cmdshell', 1
reconfigure with override
exec sp_configure 'show advanced options', 0
reconfigure with override
go

begin
declare @ipline varchar(200)
declare @pos int
declare @ip varchar(40)
set nocount on
set @ip = null
if object_id('tempdb#temp') is not null drop table #temp
create table #temp (ipline varchar(200))
insert #temp exec masterxp_cmdshell 'ipconfig'
select @ipline = ipline
from #temp
where upper (ipline) like '%IPv4 地址%'--这里需要注意一下，系统不同这里的匹配值就不同
if @ipline is not null
begin
set @pos = charindex (':',@ipline,1);
set @ip = rtrim(ltrim(substring (@ipline ,
@pos + 1 ,
len (@ipline) - @pos)))
end
select distinct(rtrim(ltrim(substring (@ipline ,
@pos + 1 ,
len (@ipline) - @pos)))) as ipaddress from #temp
drop table #temp
set nocount off
end
go

---