MySQL查询需求：按余额类型聚合客户并按规则排序拼接

现有transactions表，包含customer（客户）、debit（借方）、credit（贷方）字段，表数据如下：

需求 #

1. 计算每个客户的余额：SUM(debit) - SUM(credit)
2. 判断余额类型bal_type：余额>0为positive，否则为negative
3. 按bal_type分组，将同组客户按规则排序后拼接成字符串：
   • positive组：按余额降序排列
   • negative组：按余额升序排列

尝试的SQL语句 #

SELECT  
    CASE WHEN (SUM(debit)-SUM(credit))<0 THEN "negative"  
    ELSE "positive" END AS bal_type, 
    customer 
FROM transactions 
GROUP BY customer

当前输出 #

期望输出 #

解决方案 #

MySQL/MariaDB 版本 #

SELECT 
    bal_type,
    GROUP_CONCAT(customer ORDER BY balance DESC SEPARATOR ',') AS 客户
FROM (
    SELECT 
        customer,
        SUM(debit) - SUM(credit) AS balance,
        CASE WHEN SUM(debit) - SUM(credit) > 0 THEN 'positive' ELSE 'negative' END AS bal_type
    FROM transactions
    GROUP BY customer
) t
GROUP BY bal_type
ORDER BY bal_type DESC;

PostgreSQL 版本 #

SELECT 
    bal_type,
    STRING_AGG(customer, ',' ORDER BY 
        CASE bal_type 
            WHEN 'positive' THEN balance DESC 
            ELSE balance ASC 
        END) AS 客户
FROM (
    SELECT 
        customer,
        SUM(debit) - SUM(credit) AS balance,
        CASE WHEN SUM(debit) - SUM(credit) > 0 THEN 'positive' ELSE 'negative' END AS bal_type
    FROM transactions
    GROUP BY customer
) t
GROUP BY bal_type
ORDER BY bal_type DESC;

说明 #

1. 子查询先计算每个客户的余额和对应的bal_type
2. 外层查询按bal_type分组，使用字符串聚合函数（GROUP_CONCAT/STRING_AGG），并在聚合时指定排序规则：
   • positive组按余额降序排列客户
   • negative组按余额升序排列客户

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