PostgreSQL多表关联查询及端口、IP数据过滤需求

涉及表结构 #

• node表：mac、switch、port、vlan、active、oui
• node_ip表：mac、ip、dns
• device表：ip、dns、name、mac
• device_port表：ip、port、dns
• device_port_vlan表：ip、port、vlan

需求说明 #

关联上述表数据后，过滤两类数据：

1. 关联MAC数量超过2的交换机端口
2. 对应行数超过1的IP
   最终输出报表包含：节点name、IP、mac、vlan、port、switch信息。

最终查询语句 #

WITH port_mac_count AS (
    -- 统计每个交换机端口关联的唯一MAC数量，过滤MAC数>2的端口
    SELECT switch, port, COUNT(DISTINCT mac) AS mac_count
    FROM node
    GROUP BY switch, port
    HAVING COUNT(DISTINCT mac) <= 2
),
ip_row_count AS (
    -- 统计每个IP的关联行数，过滤行数>1的IP
    SELECT ip, COUNT(*) AS row_count
    FROM node_ip
    GROUP BY ip
    HAVING COUNT(*) <= 1
)
SELECT 
    d.name,
    ni.ip,
    n.mac,
    n.vlan,
    n.port,
    n.switch
FROM node n
-- 关联节点IP表
JOIN node_ip ni ON n.mac = ni.mac
-- 关联设备表获取节点名称（按ip+dns匹配确保关联准确性）
JOIN device d ON ni.ip = d.ip AND ni.dns = d.dns
-- 筛选符合MAC数量要求的端口
JOIN port_mac_count pmc ON n.switch = pmc.switch AND n.port = pmc.port
-- 筛选符合行数要求的IP
JOIN ip_row_count irc ON ni.ip = irc.ip
-- 可选：仅保留活跃节点
WHERE n.active = true;

查询逻辑说明 #

1. port_mac_count：通过分组统计每个交换机端口的唯一MAC数量，只保留MAC数≤2的端口，过滤多MAC占用的端口。
2. ip_row_count：统计每个IP在node_ip表中的记录数，只保留记录数≤1的IP，避免同一IP对应多个节点的情况。
3. 主查询通过多表关联整合节点基础信息、IP信息、设备名称，同时通过CTE筛选出符合条件的端口和IP，最终输出需求报表字段。

示例格式化输出 #

内容的提问来源于stack exchange，提问作者James-C1