从IP地址列表中获取最高IP的查询。

Question

我在一个表中有一个IP地址列表以及客户端主机名，按照如下方式使用一个foriegn密钥字段进行分组：

ReqID, Hostname, IPAddress
4, Server01, 192.168.10.5
4, Server02, 192.168.10.6
4, Server10, 192.168.10.7
4, Server15, 192.168.10.8
4, Server18, 192.168.10.9
4, Server23, 192.168.10.10

IP以varchars的形式存储。

我希望为特定的ReqID获得最高分配的IP。我该怎么做？

编辑:我有SQL 2005

Answer 1

您可以使用以下函数将IP转换为数字：

--Author: Faisal Khan (http://www.stardeveloper.com)
CREATE FUNCTION [dbo].[ConvertIPToLong](@IP varchar(15))
RETURNS bigint
AS
BEGIN
    DECLARE @Long bigint
    SET @Long = CONVERT(bigint, PARSENAME(@IP, 4)) * 256 * 256 * 256 +
        CONVERT(bigint, PARSENAME(@IP, 3)) * 256 * 256 +
        CONVERT(bigint, PARSENAME(@IP, 2)) * 256 +
        CONVERT(bigint, PARSENAME(@IP, 1))

    RETURN (@Long)
END

(来自这里)

然后

select reqid, max([dbo].ConvertIPToLong(IPAddress)) as maxIP
from your_table
group by reqID

编辑:可以使用的相同来源的

-- Author: Faisal Khan (http://www.stardeveloper.com)
CREATE FUNCTION [dbo].[ConvertLongToIP](@Long bigint)
RETURNS varchar(15)
AS
BEGIN
    DECLARE @IP varchar(15)
    DECLARE @TempLong bigint
    DECLARE @Temp bigint

    SET @TempLong = @Long
    SET @Temp = @TempLong / (256 * 256 * 256)
    SET @TempLong = @TempLong - (@Temp * 256 * 256 * 256)
    SET @IP = CONVERT(varchar(3), @Temp) + '.'
    SET @Temp = @TempLong / (256 * 256)
    SET @TempLong = @TempLong - (@Temp * 256 * 256)
    SET @IP = @IP + CONVERT(varchar(3), @Temp) + '.'
    SET @Temp = @TempLong / 256
    SET @TempLong = @TempLong - (@Temp * 256)
    SET @IP = @IP + CONVERT(varchar(3), @Temp) + '.'
    SET @Temp = @TempLong
    SET @TempLong = @TempLong - @Temp
    SET @IP = @IP + CONVERT(varchar(3), @Temp)

    RETURN (@IP)
END 

而查询将是

select reqid, [dbo].ConvertLongToIP(max([dbo].ConvertIPToLong(IPAddress))) as maxIP
from your_table
group by reqID

Answer 2

用于Server 2008

SELECT ReqID, 
       REPLACE(MAX(CAST('/'+ IPAddress +'/' AS HIERARCHYID)).ToString(), '/', '') 
FROM   your_table 
GROUP  BY ReqID 

对于Server 2005，没有比@Florin的回答更好的方法了，尽管它缺少一个往返返回的函数。

CREATE FUNCTION [dbo].[ConvertIPToString](@IP bigint)
RETURNS varchar(15)
AS
BEGIN
    RETURN (
        SELECT LTRIM(I3) + '.'+ LTRIM(J3) + '.' + LTRIM(J2) + '.' + LTRIM(J1)
        FROM (SELECT @IP/256, @IP % 256) T1(I1, J1)
        CROSS APPLY (SELECT I1/256, I1%256) T2(I2, J2)
        CROSS APPLY (SELECT I2/256, I2%256) T3(I3, J3)
    )
END