在一个IP列中统计不同子网的数量的SQL

Question

我需要在 MS SQL 中按子网对 IP 列中所有不同子网的数量进行分组计数。例如：计算所有具有 192.168.0、192.168.1、10.10.10 等子网的 IP 数量。

非常感谢您的帮助。

- renhack

---

列存储IP地址的存储结构是什么？ - Raj More

Answer 1

这并不是非常高效的方法，但是假设地址存储在名为 IPAddress 的 varchar 列中，您可以执行以下操作：

SELECT
    SUBSTRING(IPAddress, 1, LEN(IPAddress) - CHARINDEX('.',REVERSE(IPAddress))),
    COUNT(*)
FROM
    ...
GROUP BY
    SUBSTRING(IPAddress, 1, LEN(IPAddress) - CHARINDEX('.',REVERSE(IPAddress)))

这段代码还未经过测试，所以可能存在一些偏差或者括号缺失。

基本思路是通过找到最后一个点来截取字符串的结尾。为了找到最后一个点，我们需要反转字符串并找到第一个点，这可以通过CHARINDEX函数轻松完成。为了将“第一个点”的位置转换回原始字符串中的“最后一个点”位置，需要用原始长度减去该位置。

（如果我的假设是错误的，并且它不是以文本形式存储的，则除非您还提供数据类型，否则您不太可能得到有意义的答案。）

- Tadmas

---

几乎正确。它没有区分192.168.1和192.168.2，给出+1（SUBSTRING（IPAddress，1，LEN（IPAddress）+1-CHARINDEX（'。'，REVERSE（IPAddress）））），它就可以工作了。 - Coentje

对不起，我没有注意到你的回答。 - artdanil

@Coentje 如果您想包括用于获取子字符串索引的字符串，则只需要添加“+1”。在这种情况下，最后一个八位分隔符不相关。 - artdanil

感谢 @Coentje 和 @artdanil，我应该先来这里！ :) - renhack

如果您想按部分进行拆分，我发现最好的方法是使用数字表连接并使用它来处理拆分 - 例如http://www.sqlexamples.info/SQL/tally_table_use1.htm。 - eftpotrm

Answer 2

如果你使用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

- Scot Hauder

---

使用公共表达式和幽默的变量名是加分项，但当子网中只有1个IP时返回2的计数列则是扣分项。 - Dane

我只是举了一个例子，如果你使用无类地址，仅仅切掉最后一个八位字节是不够的。我编辑了我的答案以允许可变子网掩码。 - Scot Hauder

Answer 3

假设您将IP地址存储在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

- artdanil