如何编写PostgreSQL查询统计父子关联表的设备总数及明细？

根据你描述的场景，我假设表结构如下（若实际结构不同，可调整字段名）：

• parent 表：包含父节点信息，字段为 parent_id（主键）、parent_name（父节点名称）
• child 表：包含子节点信息，字段为 child_id（主键）、parent_id（关联父表的外键）、child_name（子节点名称）
• device 表：包含设备信息，字段为 device_id（主键）、child_id（关联子表的外键）

实现查询的SQL语句 #

WITH child_device_stats AS (
    -- 统计每个子节点的设备数量
    SELECT 
        c.parent_id,
        c.child_name,
        COUNT(d.device_id) AS child_device_count
    FROM child c
    LEFT JOIN device d ON c.child_id = d.child_id
    GROUP BY c.parent_id, c.child_name
),
parent_total_stats AS (
    -- 统计每个父节点的总设备数量
    SELECT 
        p.parent_id,
        p.parent_name,
        COUNT(d.device_id) AS total_device_count
    FROM parent p
    LEFT JOIN child c ON p.parent_id = c.parent_id
    LEFT JOIN device d ON c.child_id = d.child_id
    GROUP BY p.parent_id, p.parent_name
)
-- 关联两个统计结果，输出最终格式
SELECT 
    pts.parent_name,
    pts.total_device_count,
    cds.child_name,
    cds.child_device_count
FROM parent_total_stats pts
JOIN child_device_stats cds ON pts.parent_id = cds.parent_id
ORDER BY pts.parent_name, cds.child_name;

简化场景（若子表直接存储设备数） #

如果你的child表本身有device_count字段（直接记录该子节点的设备数量），可以用更简洁的语句：

WITH parent_total_stats AS (
    SELECT 
        p.parent_id,
        p.parent_name,
        SUM(c.device_count) AS total_device_count
    FROM parent p
    JOIN child c ON p.parent_id = c.parent_id
    GROUP BY p.parent_id, p.parent_name
)
SELECT 
    pts.parent_name,
    pts.total_device_count,
    c.child_name,
    c.device_count AS child_device_count
FROM parent_total_stats pts
JOIN child c ON pts.parent_id = c.parent_id
ORDER BY pts.parent_name, c.child_name;

结果说明 #

执行上述语句后，会得到你预期的结果：

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