我需要在 MS SQL 中按子网对 IP 列中所有不同子网的数量进行分组计数。例如:计算所有具有 192.168.0、192.168.1、10.10.10 等子网的 IP 数量。
非常感谢您的帮助。
我需要在 MS SQL 中按子网对 IP 列中所有不同子网的数量进行分组计数。例如:计算所有具有 192.168.0、192.168.1、10.10.10 等子网的 IP 数量。
非常感谢您的帮助。
这并不是非常高效的方法,但是假设地址存储在名为 IPAddress
的 varchar 列中,您可以执行以下操作:
SELECT
SUBSTRING(IPAddress, 1, LEN(IPAddress) - CHARINDEX('.',REVERSE(IPAddress))),
COUNT(*)
FROM
...
GROUP BY
SUBSTRING(IPAddress, 1, LEN(IPAddress) - CHARINDEX('.',REVERSE(IPAddress)))
这段代码还未经过测试,所以可能存在一些偏差或者括号缺失。
基本思路是通过找到最后一个点来截取字符串的结尾。为了找到最后一个点,我们需要反转字符串并找到第一个点,这可以通过CHARINDEX
函数轻松完成。为了将“第一个点”的位置转换回原始字符串中的“最后一个点”位置,需要用原始长度减去该位置。
(如果我的假设是错误的,并且它不是以文本形式存储的,则除非您还提供数据类型,否则您不太可能得到有意义的答案。)
如果你使用CIDR,仅仅取前三个八位字节是不行的。你需要像这样做:
DECLARE @Subnet varchar(15)
DECLARE @bits int
DECLARE @VLSMSuffix int
DECLARE @IP TABLE (IPAddr varchar(15), Running binary(8))
INSERT @IP
SELECT '10.10.19.2', NULL UNION -- 00001010 00001010 00010011 00000010
SELECT '10.10.10.5', NULL UNION -- 00001010 00001010 00001010 00000101
SELECT '10.10.11.2', NULL -- 00001010 00001010 00001011 00000010
SET @Subnet = '10.10.10.0' -- 00001010 00001010 00001010 00000000
SET @VLSMSuffix = 24 -- # of bits in subnet mask
-- 10.10.11.2 is part of the 10.10.10.0/23 CIDR block
DECLARE @Fun bigint
SET @Fun = CAST(CAST(16777216 as bigint) * PARSENAME(@Subnet, 4)
+ 65536 * PARSENAME(@Subnet, 3)
+ 256 * PARSENAME(@Subnet, 2)
+ PARSENAME(@Subnet, 1) as binary(8))
UPDATE @IP
SET Running = CAST(CAST(16777216 as bigint) * PARSENAME(IPAddr, 4)
+ 65536 * PARSENAME(IPAddr, 3)
+ 256 * PARSENAME(IPAddr, 2)
+ PARSENAME(IPAddr, 1) as binary(8))
-- determine subnet mask
DECLARE @Scissors bigint
SELECT @Scissors = 4294967296 - POWER(CAST(2 AS bigint), CAST(32 AS bigint) - @VLSMSuffix)
SELECT @Subnet [Subnet], COUNT(IPAddr) [Count]
FROM @IP
WHERE @Scissors & Running = @Fun
varchar
或char
类型的字段中,解决方案可能如下所示:SELECT
"Subnet" = SUBSTRING(IPAddress, 1, LEN(IPAddress) - CHARINDEX('.', REVERSE(IPAddress))),
"IP Count" = COUNT(*)
FROM [tblIPAddress]
GROUP BY SUBSTRING(IPAddress, 1, LEN(IPAddress) - CHARINDEX('.', REVERSE(IPAddress)))
在桌子上:
IPAddress
-----------------
10.10.10.1
192.168.0.1
192.168.1.2
192.168.1.4
192.168.1.5
192.168.0.2
192.168.0.3
10.10.10.3
127.0.0.1
会产生以下结果:
Subnet IP Count
-------------------------------------------------- -----------
10.10.10 2
127.0.0 1
192.168.0 3
192.168.1 3