如何在SQL Server中按行汇总三个不同表的Amount列？

你遇到的核心问题是三个表没有可匹配的关联字段，直接用JOIN会导致匹配错误或生成大量冗余数据。下面分两种常见场景给出解决方案：

场景1：按行序对应三个表的Amount并汇总（假设行的顺序有业务意义） #

如果需要把A表第1行、B表第1行、C表第1行的Amount相加，第2行同理，可通过生成行号来对齐行：

支持窗口函数的数据库（如PostgreSQL、SQL Server、MySQL 8.0+） #

SELECT
    COALESCE(a.row_idx, b.row_idx, c.row_idx) AS row_index,
    COALESCE(a.Amount, 0) + COALESCE(b.Amount, 0) + COALESCE(c.Amount, 0) AS total_amount
FROM
    (SELECT Amount, ROW_NUMBER() OVER () AS row_idx FROM A) a
FULL OUTER JOIN
    (SELECT Amount, ROW_NUMBER() OVER () AS row_idx FROM B) b ON a.row_idx = b.row_idx
FULL OUTER JOIN
    (SELECT Amount, ROW_NUMBER() OVER () AS row_idx FROM C) c ON COALESCE(a.row_idx, b.row_idx) = c.row_idx
ORDER BY row_index;

关键说明：

• ROW_NUMBER() OVER ()：为每个表的行生成连续序号，确保按原始行序对齐
• FULL OUTER JOIN：兼容三个表行数不一致的情况，不会丢失任何一行的数据
• COALESCE()：把NULL值转为0，避免因某表无对应行导致汇总结果为NULL

不支持FULL OUTER JOIN的数据库（如MySQL 5.x） #

可以先收集所有可能的行号，再用左连接实现：

WITH all_row_ids AS (
    SELECT row_idx FROM (SELECT ROW_NUMBER() OVER () AS row_idx FROM A) a
    UNION
    SELECT row_idx FROM (SELECT ROW_NUMBER() OVER () AS row_idx FROM B) b
    UNION
    SELECT row_idx FROM (SELECT ROW_NUMBER() OVER () AS row_idx FROM C) c
)
SELECT
    ar.row_idx,
    COALESCE(a.Amount, 0) + COALESCE(b.Amount, 0) + COALESCE(c.Amount, 0) AS total_amount
FROM all_row_ids ar
LEFT JOIN (SELECT Amount, ROW_NUMBER() OVER () AS row_idx FROM A) a ON ar.row_idx = a.row_idx
LEFT JOIN (SELECT Amount, ROW_NUMBER() OVER () AS row_idx FROM B) b ON ar.row_idx = b.row_idx
LEFT JOIN (SELECT Amount, ROW_NUMBER() OVER () AS row_idx FROM C) c ON ar.row_idx = c.row_idx
ORDER BY ar.row_idx;

场景2：汇总三个表所有Amount的总金额 #

如果不需要按行对应，只是要把三个表的所有Amount加起来，用UNION ALL合并后求和即可：

SELECT SUM(Amount) AS total_all_amount
FROM (
    SELECT Amount FROM A
    UNION ALL
    SELECT Amount FROM B
    UNION ALL
    SELECT Amount FROM C
) combined_data;

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