如何使用SUM函数关联两张表生成合并的结构化查询结果？

你可以将原acc表的聚合查询作为子查询，再与address表通过name字段关联，结合不同场景实现需求：

基础关联（单条地址匹配） #

如果address表中每个name仅对应一条line记录，直接用INNER JOIN即可获取三列数据：

SELECT 
    agg.name,
    agg.eq_bal,
    addr.line
FROM (
    -- 原acc表的聚合逻辑作为子查询
    SELECT name, SUM(eq_bal) AS eq_bal 
    FROM acc 
    WHERE led_id IN (19,22,25,26) 
    GROUP BY name 
) AS agg
INNER JOIN address AS addr 
    ON agg.name = addr.name
ORDER BY agg.eq_bal ASC;

处理多地址匹配场景 #

若address表中同一name对应多条line记录，可结合聚合函数对地址数据做汇总：

示例1：拼接同一名称的所有地址 #

SELECT 
    agg.name,
    agg.eq_bal,
    GROUP_CONCAT(addr.line SEPARATOR ', ') AS combined_lines -- 注：不同数据库函数不同，如SQL Server用STRING_AGG
FROM (
    SELECT name, SUM(eq_bal) AS eq_bal 
    FROM acc 
    WHERE led_id IN (19,22,25,26) 
    GROUP BY name 
) AS agg
INNER JOIN address AS addr 
    ON agg.name = addr.name
GROUP BY agg.name, agg.eq_bal
ORDER BY agg.eq_bal ASC;

示例2：统计同一名称的地址数量 #

SELECT 
    agg.name,
    agg.eq_bal,
    COUNT(addr.line) AS line_count
FROM (
    SELECT name, SUM(eq_bal) AS eq_bal 
    FROM acc 
    WHERE led_id IN (19,22,25,26) 
    GROUP BY name 
) AS agg
INNER JOIN address AS addr 
    ON agg.name = addr.name
GROUP BY agg.name, agg.eq_bal
ORDER BY agg.eq_bal ASC;

保留无地址匹配的记录 #

如果需要保留acc表中存在但address表无匹配的name，用LEFT JOIN替换INNER JOIN即可：

SELECT 
    agg.name,
    agg.eq_bal,
    addr.line
FROM (
    SELECT name, SUM(eq_bal) AS eq_bal 
    FROM acc 
    WHERE led_id IN (19,22,25,26) 
    GROUP BY name 
) AS agg
LEFT JOIN address AS addr 
    ON agg.name = addr.name
ORDER BY agg.eq_bal ASC;

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