MySQL多子查询与自定义变量问题：IP范围合并及独立IP过滤

要解决合并IP范围并过滤范围内独立IP的问题，我们可以通过IP转数值化处理、合并重叠范围、过滤独立IP三个步骤实现，以下是具体的SQL实现和逻辑说明：

核心思路 #

1. 将IP字符串转换为整数，方便进行范围比较（MySQL中使用INET_ATON()/INET_NTOA()函数）；
2. 配对原始的起始（flag='f'）和结束（flag='l'）IP，合并重叠或相交的范围；
3. 筛选出不在任何合并范围内的独立IP（flag='a'），最终将合并范围和剩余独立IP合并输出。

完整SQL代码 #

WITH original_ranges AS (
    -- 配对所有原始的IP起始和结束范围，并转换为数值
    SELECT
        f.ip AS start_ip,
        INET_ATON(f.ip) AS start_ip_num,
        l.ip AS end_ip,
        INET_ATON(l.ip) AS end_ip_num
    FROM ips_test f
    JOIN ips_test l ON f.fol = -l.fol  -- 根据fol规则配对起始和结束IP
    WHERE f.flag = 'f' AND l.flag = 'l'
),
merged_ranges AS (
    -- 合并重叠/相交的IP范围
    SELECT
        MIN(start_ip_num) AS merged_start_num,
        MAX(end_ip_num) AS merged_end_num,
        INET_NTOA(MIN(start_ip_num)) AS merged_start_ip,
        INET_NTOA(MAX(end_ip_num)) AS merged_end_ip
    FROM (
        SELECT
            start_ip_num,
            end_ip_num,
            -- 通过窗口函数划分合并组：当前范围起始大于之前所有范围的最大结束则新建组
            SUM(CASE 
                WHEN start_ip_num > COALESCE(MAX(end_ip_num) OVER (ORDER BY start_ip_num ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING), 0) 
                THEN 1 ELSE 0 
            END) OVER (ORDER BY start_ip_num) AS group_id
        FROM original_ranges
        ORDER BY start_ip_num
    ) t
    GROUP BY group_id
)
-- 输出合并后的IP范围 + 不在任何范围内的独立IP
SELECT 'range' AS entry_type, merged_start_ip AS ip_start, merged_end_ip AS ip_end FROM merged_ranges
UNION ALL
SELECT 'single' AS entry_type, ip AS ip_start, NULL AS ip_end
FROM ips_test a
WHERE a.flag = 'a'
AND NOT EXISTS (
    -- 判断独立IP是否落在某个合并范围内
    SELECT 1
    FROM merged_ranges mr
    WHERE INET_ATON(a.ip) BETWEEN mr.merged_start_num AND mr.merged_end_num
);

代码说明 #

1. original_ranges：根据fol的正负配对规则，将每个起始IP和对应的结束IP关联，并转换为整数格式，为后续范围比较做准备；
2. merged_ranges：使用窗口函数划分合并组，对同一组内的重叠范围取最小起始和最大结束，得到无重叠的合并范围；
3. 最终查询：通过UNION ALL合并两类结果，并用NOT EXISTS过滤掉落在合并范围内的独立IP。

测试结果 #

针对你提供的测试数据，执行上述SQL后会得到如下结果：

注意事项 #

• 若使用非MySQL数据库，需替换INET_ATON()/INET_NTOA()为对应数据库的IP转换函数（如PostgreSQL可直接使用inet类型进行范围比较）；
• 若fol的配对规则与示例不同，需调整original_ranges中的关联条件。

内容的提问来源于stack exchange，提问作者Nyx