php - MYSQL 中的 UNION 和合并列

Question

我正在从 mysql 中的两个表中获取数据。我有这样的查询:

SELECT SUM(IF(debit IS NULL, 0, debit)) - SUM(IF(credit IS NULL, 0, credit)) as amount, th.country_id, cl.currency_name
FROM account_treasury_hq th
LEFT JOIN system_country_list cl ON cl.country_id=th.country_id
WHERE th.company_id='$company_id'
GROUP BY th.country_id

UNION

SELECT SUM(CASE WHEN ce_type = 'IN' or ce_type is NULL then payment_amount
                WHEN ce_type = 'OUT' then - payment_amount
                END) as amount,
source_country_id as country_id, cl.currency_name
FROM customer_payment_options cpo
LEFT JOIN system_country_list cl ON cl.country_id=cpo.source_country_id
WHERE cpo.company_id='$company_id'
GROUP BY cpo.source_country_id

结果

Array
(
[0] => Array
    (
        [amount] => -345
        [country_id] => 40
        [currency_name] => Canadian dollar
    )

[1] => Array
    (
        [amount] => 93
        [country_id] => 210
        [currency_name] => Sri Lankan rupee
    )

[2] => Array
    (
        [amount] => 46.5
        [country_id] => 236
        [currency_name] => United States dollar
    )

[3] => Array
    (
        [amount] => 916249.8999999999
        [country_id] => 40
        [currency_name] => Canadian dollar
    )

[4] => Array
    (
        [amount] => -6670.1
        [country_id] => 210
        [currency_name] => Sri Lankan rupee
    )

[5] => Array
    (
        [amount] => 2017.67
        [country_id] => 236
        [currency_name] => United States dollar
    )

)

在结果数组中，您可以看到相同的 country_id。我想将那些相同的国家/地区 ID 与 1 合并。这意味着，每个国家/地区 ID 应该只有每个金额信息。(数量应该通过添加多个国家来添加)有什么办法可以在 PHP 或 mysql 本身中做到这一点？感谢您提供任何帮助。

要求的结果

Array
(
[0] => Array
    (
        [amount] => 915904.89
        [country_id] => 40
        [currency_name] => Canadian dollar
    )

[1] => Array
    (
        [amount] => -6577.1
        [country_id] => 210
        [currency_name] => Sri Lankan rupee
    )

[2] => Array
    (
        [amount] => 2064.17
        [country_id] => 236
        [currency_name] => United States dollar
    )
)

Answer 1

只需在外部查询中将其全部选中，然后按 country_id 对金额求和并分组

SELECT 
    SUM(amount) country_id, currency_name
FROM 
(   SELECT 
        SUM(IF(debit IS NULL, 0, debit)) - SUM(IF(credit IS NULL, 0, credit)) as amount,
        th.country_id, 
        cl.currency_name
    FROM account_treasury_hq th
    LEFT JOIN system_country_list cl ON cl.country_id=th.country_id
    WHERE th.company_id='$company_id'
    GROUP BY th.country_id

    UNION

    SELECT 
        SUM(CASE 
               WHEN ce_type = 'IN' or ce_type is NULL then payment_amount
               WHEN ce_type = 'OUT' then - payment_amount
               END
        ) as amount,
        source_country_id as country_id, 
        cl.currency_name
    FROM customer_payment_options cpo
    LEFT JOIN system_country_list cl ON cl.country_id=cpo.source_country_id
    WHERE cpo.company_id='$company_id'
    GROUP BY cpo.source_country_id
) t
GROUP BY country_id