finddalao_sol/src/library/sql_xianyu001_myxiugai.sql

WITH RECURSIVE excluded_ips(ip) AS(
  SELECT 'ip20'  
  UNION ALL
  SELECT 'ip21' 
),

 ConnectedIPs AS (
    -- 递归基: 初始化起始 IP 和结束 IP
    -- 这些是递归查询的起点
    SELECT 
        1 AS level,  -- 记录当前递归层级（初始为1）
        'ip0' AS from_ip,  -- 起始 IP
        NULL AS to_ip,  -- 初始时没有 to ip
        0 AS addition_info  -- 初始时没有标记为连接数过多
    UNION ALL
    SELECT 
        1 AS level,  -- 记录当前递归层级（初始为1）  
        'ip1' AS from_ip,  -- 起始 IP
        NULL AS to_ip,  -- 初始时没有 to ip
        0 AS addition_info  -- 初始时没有标记为连接数过多
    -- 递归步骤：查找与已知 IP 相连的其他 IP
    UNION ALL
    SELECT 
        ConnectedIPs.level + 1 AS level,  -- 下一层级的递归层级（当前层级+1）
        ConnectedIPs.from_ip AS from_ip,  -- 当前层级的 from ip
        table_01.to_ip AS to_ip,  -- 新的 to ip
        CASE 
            -- 检査当前 IP 的连接数是否超过s
            WHEN (SELECT COUNT(*) FROM table_01   WHERE table_01.from_ip = ConnectedIPs.from_ip OR table_01.to_ip = ConnectedIPs.from_ip) >= 5 THEN 1
            ELSE 0
        END AS addition_info
    FROM ConnectedIPs  
    JOIN table_01  ON (table_01.from_ip = ConnectedIPs.from_ip OR table_01.to_ip = ConnectedIPs.from_ip)
    -- 递归条件:确保递归深度不超过最大值并且新Ip不在排除列表中

    WHERE ConnectedIPs.level < 5  -- 最大递归深度为5
    AND NOT EXISTS (SELECT 1 FROM excluded_ips  WHERE excluded_ips.ip = table_01.from_ip OR excluded_ips.ip = table_01.to_ip)
    )
-- 选择最终结果


SELECT 
    ConnectedIPs.level,
    ConnectedIPs.from_ip,
    ConnectedIPs.to_ip,
    NULL AS unique_hash,  -- 需要替换为你实际的 unique hash 生成逻辑
    CURRENT_TIMESTAMP AS time,  -- 当前时间戳作为时间字段
    NULL AS data,  -- 需要替换为你实际的数据
    NULL AS from_pos,  -- 需要替换为你实际的位置信息
    NULL AS to_pos,  -- 需要替换为你实际的位置信息
    ConnectedIPs.addition_info  -- 标记是否连接数过多
FROM ConnectedIPs  
WHERE ConnectedIPs.level <= 5;  -- 确保只选择不超过最大递归深度的结果