有两个表 一个表 UserLog
字段
UserId, LogIp, LogTime
0001 127.0.0.1 2009-1-1
0002 127.0.0.2 2009-2-2
一个表 IpInfo IP数据库
FromIp ToIp Descript
127.0.0.1 127.0.0.2 财务部
127.0.0.3 127.0.0.4 人力资源我想要的结果就是能够将UserLog 中的IP地址与描述挂上关系,能够显示用户登录的IP是属于哪个地方
字段
UserId, LogIp, LogTime
0001 127.0.0.1 2009-1-1
0002 127.0.0.2 2009-2-2
一个表 IpInfo IP数据库
FromIp ToIp Descript
127.0.0.1 127.0.0.2 财务部
127.0.0.3 127.0.0.4 人力资源我想要的结果就是能够将UserLog 中的IP地址与描述挂上关系,能够显示用户登录的IP是属于哪个地方
from UserLog as u,IpInfo IP as i
where u.LogIp=i.FromIp
你试试!
create table UserLog(UserId varchar(10), LogIp varchar(20), LogTime datetime)
goinsert UserLog select '0001', '127.0.0.1', '2009-1-1'
insert UserLog select '0002', '127.0.0.2', '2009-2-2'
go create table IpInfo(FromIp varchar(20), ToIp varchar(20), Descript varchar(20) )
insert IpInfo select '127.0.0.1', '127.0.0.2', '财务部'
insert IpInfo select '127.0.0.3', '127.0.0.4', '人力资源'
go
--我想要的结果就是能够将UserLog 中的IP地址与描述挂上关系,能够显示用户登录的IP是属于哪个地方
select a.*,b.Descript from UserLog a,IpInfo b where a.logip between b.FromIp and b.ToIpgo
drop table UserLog,IpInfo/*
UserId LogIp LogTime Descript
---------- -------------------- ------------------------------------------------------ --------------------
0001 127.0.0.1 2009-01-01 00:00:00.000 财务部
0002 127.0.0.2 2009-02-02 00:00:00.000 财务部(所影响的行数为 2 行)
*/
select a.userid,a.logip,convert(varchar(10),a.logtime,120) LogTime,b.Descript from UserLog a,IpInfo b where a.logip between b.FromIp and b.ToIp
a.UserId,
b.Descript,
a.LogIp,
a.LogTime
from UserLog a
left join IpInfo b
on right(a.LogIp)
between left(reverse(FromIp),charindex('.',reverse(FromIp))-1)
and left(reverse(ToIp),charindex('.',reverse(ToIp))-1)
goinsert UserLog select '0001', '127.0.0.21', '2009-1-1'
insert UserLog select '0002', '127.0.0.112', '2009-2-2'
go create table IpInfo(FromIp varchar(20), ToIp varchar(20), Descript varchar(20) )
insert IpInfo select '127.0.0.1', '127.0.0.22', '财务部'
insert IpInfo select '127.0.0.30', '127.0.0.120', '人力资源'
go--查询
select
a.UserId,
b.Descript,
a.LogIp,
a.LogTime
from UserLog a
left join IpInfo b
on cast(reverse(left(reverse(a.LogIp),charindex('.',reverse(LogIp))-1)) as int)
between cast(reverse(left(reverse(b.FromIp),charindex('.',reverse(b.FromIp))-1)) as int)
and cast(reverse(left(reverse(b.ToIp),charindex('.',reverse(b.ToIp))-1)) as int)--drop table UserLog,IpInfo/**
UserId Descript LogIp LogTime
---------- -------------------- -------------------- ------------------------------------------------------
0001 财务部 127.0.0.21 2009-01-01 00:00:00.000
0002 人力资源 127.0.0.112 2009-02-02 00:00:00.000(所影响的行数为 2 行)
**/
--> (让你望见影子的墙)生成测试数据,时间:2009-03-08
if not object_id('userlog') is null
drop table userlog
Go
Create table userlog([UserId] nvarchar(4),[LogIp] nvarchar(9),[LogTime] Datetime)
Insert usrlog
select N'0001',N'127.0.0.1','2009-1-1' union all
select N'0002',N'127.0.0.2','2009-2-2'
Go
Select * from userlog
--> (让你望见影子的墙)生成测试数据,时间:2009-03-08
if not object_id('ipinfo') is null
drop table ipinfo
Go
Create table ipinfo([FromIp] nvarchar(9),[ToIp] nvarchar(9),[Descript] nvarchar(4))
Insert ipinfo
select N'127.0.0.1',N'127.0.0.2',N'财务部' union all
select N'127.0.0.3',N'127.0.0.4',N'人力资源'
Go
Select * from ipinfoselect userid,descript,logtime
from userlog,ipinfo
where logip between fromip and toip0001 财务部 2009-01-01 00:00:00.000
0002 财务部 2009-02-02 00:00:00.000
是同一IP,但between的话会得不到正确值。
段的 比如 财务部可能是 192.3.1.1 - 192.5.3.2 这样的情况怎么实现呢。
'61.11.11.2' 要变为 ‘061.011.011.002'
declare @a varchar(20),@b varchar(20)set @a ='61.11.11.2'
select top 30 id=identity(int,1,1) into # from syscolumnsselect @b=isnull(@b+'.','')+
right('00'+substring(@a,id,charindex('.',@a+'.',id+1)-id),3)
from #
where substring('.'+@a,id,1)='.'
select @b
drop table #
--result
/*
--------------------
061.011.011.002(所影响的行数为 1 行)
*/
来个投机取巧点的:
-------------------------------------------------------------------------
declare @str varchar(20)
set @str='61.11.11.2'select right('000'+ParseName(@str,4),3)+'.'
+right('000'+ParseName(@str,3),3)+'.'
+right('000'+ParseName(@str,2),3)+'.'
+right('000'+ParseName(@str,1),3)/*
---------------
061.011.011.002
*/
insert into tb1 values('0001' , '127.0.0.1' ,'2009-1-1')
insert into tb1 values('0002' , '127.0.0.2' ,'2009-2-2')
create table tb2(FromIp varchar(15), ToIp varchar(15) , Descript varchar(10))
insert into tb2 values('127.0.0.1' , '127.0.0.2' , '财务部')
insert into tb2 values('127.0.0.3' , '127.0.0.4' , '人力资源')
goselect m.* , n.* from
(
select right('000'+ParseName(LogIp,4),3)+'.'
+right('000'+ParseName(LogIp,3),3)+'.'
+right('000'+ParseName(LogIp,2),3)+'.'
+right('000'+ParseName(LogIp,1),3) LogIp , UserId , LogTime
from tb1
) m ,
(
select right('000'+ParseName(FromIp,4),3)+'.'
+right('000'+ParseName(FromIp,3),3)+'.'
+right('000'+ParseName(FromIp,2),3)+'.'
+right('000'+ParseName(FromIp,1),3) FromIp ,
right('000'+ParseName(ToIp,4),3)+'.'
+right('000'+ParseName(ToIp,3),3)+'.'
+right('000'+ParseName(ToIp,2),3)+'.'
+right('000'+ParseName(ToIp,1),3) ToIp , Descript
from tb2
) n
where m.LogIp between n.FromIp and n.ToIpdrop table tb1 , tb2 /*
LogIp UserId LogTime FromIp ToIp Descript
--------------- ---------- --------------- --------------- --------------- ----------
127.000.000.001 0001 2009-1-1 127.000.000.001 127.000.000.002 财务部
127.000.000.002 0002 2009-2-2 127.000.000.001 127.000.000.002 财务部(所影响的行数为 2 行)*/如果你的数据量特别大,可把上面的两个子查询查到的数据插入到临时表,然后对临时表进行查询.
create function GetFullIP(@str varchar(20))
returns varchar(20)
as
begin
declare @i int,@str1 varchar(20),@strR varchar(20)
select @i =0,@str1 ='',@strR=''
set @i=charindex('.',@str)
while @i>=1
begin
set @str1=right(1000+left(@str,@i-1),3)
set @strR=@strR+@str1+'.'
set @str=right(@str,len(@str)-@i)
set @i=charindex('.',@str)
end
set @strR=@strR+right(1000+@str,3)
return @strR
end
go
select dbo.GetFullIP('127.0.0.1')
/*
--------------------
127.000.000.001(所影响的行数为 1 行)
*/select a.userid,a.logip,a.logtime,b.Descript from UserLog a,IpInfo b where dbo.GetFullIP(a.logip) between dbo.GetFullIP(b.FromIp) and dbo.GetFullIP(b.ToIp)
而不是使用varchar,只在展现到客户界面时转为字符串显示。一句话,是数据类型设计错误给你造成了
麻烦。
我是外行人说外行话了哈,大家不要笑。
记录, UserLog 里面有1万条记录,查询了N久都出不了结果