如何通过IP进行地区划分

[MOTA]

日志表LOG
包含列 IP 为nvarchar类型
其中数据为 192.168.1.1 类似的情况

然后另外有地区IP划分表 DQ
包含列 STARTIP 起始IP ENDIP 终止IP PID 地区编码
其中STARTIP 跟 ENDIP 都是 int类型 如 201192021 这样的
是 IP转换成的十进制


现在要求统计 地区IP数据

我毫无头绪啊..求指教

[-晴天]

将日志表IP转换为数值后,与DQ表连接后统计.
select a.地区编码,count(*) from DQ a inner join log b on b.转换后的ip数值 between a.起始IP and a.终止IP
group by a.地区编码

[中国风]

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[f_IP2Int]') and xtype in (N'FN', N'IF', N'TF'))
drop function [dbo].[f_IP2Int]
GO

--1. 字符串IP地址转换成IP数值函数。
CREATE FUNCTION dbo.f_IP2Int(
@ip char(15)
)RETURNS bigint
AS
BEGIN
	DECLARE @re bigint
	SET @re=0
	SELECT @re=@re+LEFT(@ip,CHARINDEX('.',@ip+'.')-1)*ID
		,@ip=STUFF(@ip,1,CHARINDEX('.',@ip+'.'),'')
	FROM(
		SELECT ID=CAST(16777216 as bigint)
		UNION ALL SELECT 65536
		UNION ALL SELECT 256
		UNION ALL SELECT 1)a
	RETURN(@re)
END
GO


/*===========================================================*/


if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[f_Int2IP]') and xtype in (N'FN', N'IF', N'TF'))
drop function [dbo].[f_Int2IP]
GO

--1. 字符串IP地址转换成IP数值函数。
CREATE FUNCTION dbo.f_Int2IP(
@IP bigint
)RETURNS varchar(15)
AS
BEGIN
	DECLARE @re varchar(15)
	SET @re=''
	SELECT @re=@re+'.'+CAST(@IP/ID as varchar)
		,@IP=@IP%ID
	from(
		SELECT ID=CAST(16777216 as bigint)
		UNION ALL SELECT 65536
		UNION ALL SELECT 256
		UNION ALL SELECT 1)a
	RETURN(STUFF(@re,1,1,''))
END
GO

[中国风]

把Log的IP轉為bigint再比較,分組

[--小F--]

if   exists   (select   *   from   dbo.sysobjects   where   id   =   object_id(N'[dbo].[f_IP2Int]')   and   xtype   in   (N'FN',   N'IF',   N'TF'))   
  drop   function   [dbo].[f_IP2Int]   
  GO   
    
  /**//*--字符型   IP   地址转换成数字   IP   
    
  --邹建   2004.08(引用请保留此信息)--*/   
    
  /**//*--调用示例   
    
  select   dbo.f_IP2Int('192.168.0.11')   
  select   dbo.f_IP2Int('12.168.0.1')   
  --*/   
  CREATE   FUNCTION   f_IP2Int(   
  @ip   char(15)   
  )RETURNS   bigint   
  AS   
  BEGIN   
  DECLARE   @re   bigint   
  SET   @re=0   
  SELECT   @re=@re+LEFT(@ip,CHARINDEX('.',@ip+'.')-1)*ID   
  ,@ip=STUFF(@ip,1,CHARINDEX('.',@ip+'.'),'')   
  FROM(   
  SELECT   ID=CAST(16777216   as   bigint)   
  UNION   ALL   SELECT   65536   
  UNION   ALL   SELECT   256   
  UNION   ALL   SELECT   1)A   
  RETURN(@re)   
  END   
  GO   
    
    
    
  if   exists   (select   *   from   dbo.sysobjects   where   id   =   object_id(N'[dbo].[f_Int2IP]')   and   xtype   in   (N'FN',   N'IF',   N'TF'))   
  drop   function   [dbo].[f_Int2IP]   
  GO   
    
  /**//*--数字   IP   转换成格式化   IP   地址   
    
  --邹建   2004.08(引用请保留此信息)--*/   
    
  /**//*--调用示例   
    
  select   dbo.f_Int2IP(3232235531)   
  select   dbo.f_Int2IP(212336641)   
  --*/   
  CREATE   FUNCTION   f_Int2IP(   
  @IP   bigint   
  )RETURNS   varchar(15)   
  AS   
  BEGIN   
  DECLARE   @re   varchar(15)   
  SET   @re=''   
  SELECT   @re=@re+'.'+CAST(@IP/ID   as   varchar)   
  ,@IP=@IP%ID   
  from(   
  SELECT   ID=CAST(16777216   as   bigint)   
  UNION   ALL   SELECT   65536   
  UNION   ALL   SELECT   256   
  UNION   ALL   SELECT   1)a   
  RETURN(STUFF(@re,1,1,''))   
  END   

[--小F--]

数字转IP地址自定义函数(附：相互转换) 

----------------------------------------------------------------------------------

-- Author : htl258(Tony)

-- Date   : 2010-06-19 10:34:31

-- Version: Microsoft SQL Server 2008 (RTM) - 10.0.1600.22 (Intel X86) 

--          Jul  9 2008 14:43:34 

--          Copyright (c) 1988-2008 Microsoft Corporation

--          Developer Edition on Windows NT 5.1 <X86> (Build 2600: Service Pack 2)

-- Blog   : http://blog.csdn.net/htl258

-- Subject: 数字转IP地址函数

----------------------------------------------------------------------------------

--数字转IP地址函数：

IF OBJECT_ID('dbo.fn_IP2Str')>0

    DROP FUNCTION dbo.fn_IP2Str

GO

CREATE FUNCTION [dbo].[fn_IP2Str] (

    @InIP BIGINT

)

RETURNS NVARCHAR(15)

AS

BEGIN

    IF @InIP IS NULL

       RETURN '0.0.0.0'

    DECLARE @ip BIGINT

    SET @ip = @InIP

    SET @ip = @ip + 0x100000000

    RETURN

       CAST(((@ip & 0xff000000) / 0x1000000) AS NVARCHAR(3)) + '.' +

       CAST(((@ip & 0xff0000) / 0x10000) AS NVARCHAR(3)) + '.' +

       CAST(((@ip & 0xff00) / 0x100) AS NVARCHAR(3)) + '.' +

       CAST((@ip & 0xff) AS NVARCHAR(3))

END

GO

 

--调用示例：

SELECT dbo.fn_IP2Str(4294967295)

/*

---------------

255.255.255.255

 

(1 行受影响)

*/

SELECT dbo.fn_IP2Str(0)

/*

---------------

0.0.0.0

 

(1 行受影响)

*/

[--小F--]

如果是MYSQL的话就比较简单了


1.将IP地址转化为数字

select inet_aton('210.30.0.103');

2.将数字转化为IP地址

select inet_ntoa(3525181543);

[快溜]

parsename