给定子网范围和IP列表，选择IP介于其间的所有行

4 回答

• 1

  这不是一个完整的解决方案，但更多的是另一个设计的想法，我在思考而不是做典型的SQL比较，为什么不尝试使用逻辑比较 . 知道很少的sql实现我试着用比特式的比较来愚弄，（和bigint一样）

  有很多优化要做，但我认为它可能会有所帮助，

  一个小的演示，我比较4个ip（192.168.1.1和3个），我使用它们作为bigint导致int太小，我需要使用逻辑按位比较，（更多信息在这里http://msdn.microsoft.com/en-us/library/ms174965.aspx）

  select * from (
      select cast(192168001001 as bigint) as ip union all
      select cast(192168001002 as bigint) as ip union all
      select cast(192168002001 as bigint) as ip union all
      select cast(192168002002 as bigint) as ip
  ) as ip_table
  where ip & cast(192168001000 as bigint) = cast(192168001000 as bigint)
  

  你可以看到我（和/＆）的IP和网络地址然后我把它与网络地址进行比较，如果它匹配，它就属于这个范围

  纠正我，如果我错了，我需要更多地考虑这个，非常有趣的东西

  

  编辑：正如下面评论的那样，bigint对于IPv6来说太小了，所以这不可行，按位（AND）操作不能用二进制数据类型完成，它只接受整数类型...

  回复于 2024-05-02T12:17:09+08:00
• 0

  我一直在看你的SQL小提琴，玩弄有问题的查询，

  要100％清除，您需要查询以查找主机地址列表所属的所有范围 .

  所以你可以表现得好像你的主机是一个列表/数据表，然后内部连接子网到它上面（如果你需要它甚至没有子网就显示左连接）

  select *
  from (
    select '192.168.001.001' as ip union
    select'192.168.005.015') as hosts
  inner join subnet
    on ip between ipv4_network and ipv4_broadcast
  

  我得到4个结果（有两个子网匹配每个记录）

  回复于 2024-05-02T12:17:09+08:00
• 1

  您是否考虑将ipv6和ipv4格式存储在一列中？

  Storing IP addresses in Microsoft SQL Server

  它需要转换任意源数据以供您比较（或其他方式），但至少您可以避免需要两个单独的查询来检查 .

  然后，我倾向于从源数据（FOR XML？）形成CTE，然后加入到数据库表（子网） .

  回复于 2024-05-02T12:17:09+08:00
• 1

  我倾向于使用[xml]或[heirarchyid]（http://technet.microsoft.com/en-us/library/bb677290.aspx）来解决这个问题，并将数据视为树 . 然后使用[tree]构建基于现有子网的树变得相对简单 . [run] @subnet方法运行树并找到与@subnet匹配的节点 . 通过将数据视为树（实际上是它）并构建常见的树处理（递归）方法，您应该能够轻松地找到一个点，在该点找到一个节点（如果存在）或插入它并获取下一个节点先前的节点 .

  如果我感兴趣的话，我可以提供更详细的例子，但它不是一个简单的解决方案，所以我不打算花时间在它上面 . 我在这里展示的是一个简单的原型，它找到的节点是输入掩码的父节点（作为简单匹配） . 我只提供这个例子，但如果解决方案让您感兴趣，我可以提供更多细节，或者您可以很容易地看到如何使用这些技术构建解决方案 .

  和平，凯瑟琳

  use [test_01];
  
      go
  
      if schema_id(N'tree') is null
          execute (N'create schema tree');
  
      go
  
      if object_id(N'[tree].[run]',
                   N'FN') is not null
          drop function [tree].[run];
  
      go
  
      create function [tree].[run] (
          @network      [xml],
          @mask_to_find [sysname],
          @position     [sysname]
      )
      returns [sysname]
      as
          begin
              declare @quad [sysname] = substring(@mask_to_find,
                          0,
                          charindex(N'.',
                                    @mask_to_find,
                                    0));
  
              set @mask_to_find = substring(@mask_to_find,
                                            charindex(N'.', @mask_to_find, 0) + 1,
                                            len(@mask_to_find));
              set @network = @network.query('/*[@quad=sql:variable("@quad")]/*');
  
              if(@network.value('count (/*)',
                                'int') > 0)
                  begin
                      set @position = coalesce(@position + N'.', N'') + @quad;
                  end
              else
                  set @position = coalesce(@position + N'.', N'') + N'000';
  
              if (@@nestlevel < 4)
                  return [tree].[run] (@network,
                                       @mask_to_find,
                                       @position);
  
              return @position;
          end
  
      go
  
      declare @network [xml] = N'<subnet quad="255" >
              <subnet quad="255" >
                  <subnet quad="192" />
                  <subnet quad="255" />
              </subnet>
          </subnet>
          <subnet quad="10" />';
      declare @mask_to_find [sysname] = N'255.255.190.000';
      declare @position [sysname];
  
      select [tree].[run] (@network,
                           @mask_to_find,
                           @position)
  
      go
  

  回复于 2024-05-02T12:17:09+08:00