如何合并含空值的SQL查询结果行？按币种汇总金额实现

要实现将卖出币种汇总与对应买入币种合并到同一行的需求，可通过CTE（公共表表达式）分别汇总卖出、买入数据，再建立关联来实现：

WITH sell_summary AS (
    -- 汇总每个卖出币种的总金额，同时获取该币种对应的买入币种
    -- 若同一卖出币种对应多个买入币种，可根据业务替换MAX为MIN或其他聚合逻辑
    SELECT 
        sell_currency,
        SUM(sell_amount) AS sell_amount,
        MAX(buy_currency) AS paired_buy_currency
    FROM table1
    GROUP BY sell_currency
),
buy_summary AS (
    -- 汇总每个买入币种的总金额，过滤无实际买入币种的行
    SELECT 
        buy_currency,
        SUM(buy_amount) AS buy_amount
    FROM table1
    WHERE buy_currency IS NOT NULL
    GROUP BY buy_currency
)
-- 关联卖出汇总与对应买入汇总，得到合并结果
SELECT 
    s.sell_currency,
    s.sell_amount,
    s.paired_buy_currency AS buy_currency,
    b.buy_amount
FROM sell_summary s
LEFT JOIN buy_summary b ON s.paired_buy_currency = b.buy_currency;

逻辑说明 #

1. sell_summary：按卖出币种分组，计算总卖出金额；通过MAX(buy_currency)提取该卖出币种对应的买入币种（如果存在多个对应买入币种，可根据业务需求调整聚合函数，比如用MIN取最小币种，或用字符串聚合函数保留所有对应币种）。
2. buy_summary：按买入币种分组，计算总买入金额，排除buy_currency为null的无效行。
3. 关联查询：通过LEFT JOIN将卖出汇总与对应的买入汇总关联，确保每个卖出币种的行都能保留，同时匹配到对应的买入金额。

如果业务中存在一个卖出币种对应多个买入币种的场景，且需要在同一行展示所有对应数据，可使用字符串聚合函数（如MySQL的GROUP_CONCAT、PostgreSQL的STRING_AGG）将多个买入币种和金额拼接，示例如下（以MySQL为例）：

WITH sell_summary AS (
    SELECT 
        sell_currency,
        SUM(sell_amount) AS sell_amount,
        GROUP_CONCAT(DISTINCT buy_currency) AS buy_currencies
    FROM table1
    WHERE buy_currency IS NOT NULL
    GROUP BY sell_currency
    UNION ALL
    SELECT 
        sell_currency,
        SUM(sell_amount) AS sell_amount,
        NULL AS buy_currencies
    FROM table1
    WHERE buy_currency IS NULL
    GROUP BY sell_currency
),
buy_summary AS (
    SELECT 
        buy_currency,
        SUM(buy_amount) AS buy_amount
    FROM table1
    WHERE buy_currency IS NOT NULL
    GROUP BY buy_currency
)
SELECT 
    s.sell_currency,
    s.sell_amount,
    s.buy_currencies AS buy_currency,
    GROUP_CONCAT(b.buy_amount) AS buy_amount
FROM sell_summary s
LEFT JOIN buy_summary b ON FIND_IN_SET(b.buy_currency, s.buy_currencies) > 0
GROUP BY s.sell_currency, s.sell_amount, s.buy_currencies;

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