使用 SQL 检测重叠的 IP 地址范围

Question

我正在使用一个应用程序，该应用程序具有以下用于存储 IP 地址范围的表：

IP_RANGE
--------
RANGE_ID      primary key
NETWORK_ID    foreign key into NETWORK table (not described here)
IP_ID         foreign key into IP_ADDRESS table
RANGE_TYPE    varchar, values "START" or "END"

IP_ADDRESS
----
IP_ID         primary key
IP_NETWORK    number (decimal representation of network portion of address)
IP_INTERFACE  number (decimal representation of interface portion of address)

给定的 IP 地址范围由 IP_RANGE 表中的两行表示：一行的 RANGE_TYPE 值为“START”，另一行的值为“END” "，两者的 NETWORK_ID 值相同。这些行中的每一行还指向 IP_ADDRESS 表中存储实际地址的行。

我需要编写一个 SELECT 语句，为我提供所有 NETWORK_ID，这些 NETWORK_ID 的 IP 范围具有与给定任意 IP 范围相同的任何地址。

我知道如何检查 IP_ADDRESS 表以查找位于所需范围内或之外的各个地址，但我不知道如何将搜索限制为仅具有相同 NETWORK_ID 的开始/结束对。

（我想这实际上是一个纯粹的 SQL 问题，而不是任何专门与 IP 地址相关的问题。）

Oracle Database 11g 企业版版本 11.1.0.7.0 - 64 位生产

Answer 1

您需要将 IP_RANGE 连接到自身，以便通过单个查询获取有关范围开始和范围结束的信息。您还需要将每次出现的 IP_RANGE 与 IP_ADDRESS 连接起来，以便进行 IP 地址比较；所以，总而言之，你会得到这样的东西：

SELECT ip_range_start.network_id
  FROM ip_range ip_range_start
  JOIN ip_address ip_range_start_address
    ON ip_range_start.ip_id = ip_range_start_address.ip_id
  JOIN ip_range ip_range_end
    ON ip_range_start.network_id = ip_range_end.network_id
  JOIN ip_address ip_range_end_address
    ON ip_range_end.ip_id = ip_range_end_address.ip_id
 WHERE ip_range_start.range_type = 'START'
   AND ip_range_end.range_type = 'END'
   AND (    ... -- ip_range_start_address less than or equal to end of desired IP range
         OR ... -- ip_range_end_address greater than or equal to start of desired IP range
       )
;