MySQL中基于user与curr过滤、按最长debet代码去重聚合的方法

原始交易数据 #

需求说明 #

• 过滤指定用户SX40107X和货币IDR的交易
• 以长度最长的账户代码为基准，合并该账户对应的所有借方、贷方金额
• 输出指定格式的汇总结果

实现SQL代码 #

WITH account_amounts AS (
    -- 拆分交易为账户收支记录，同时过滤目标用户和货币
    SELECT
        日期,
        debet AS account,
        金额 AS debet_amount,
        0 AS credit_amount,
        ket
    FROM 你的交易表名
    WHERE user = 'SX40107X' AND curr = 'IDR'
    
    UNION ALL
    
    SELECT
        日期,
        credit AS account,
        0 AS debet_amount,
        金额 AS credit_amount,
        ket
    FROM 你的交易表名
    WHERE user = 'SX40107X' AND curr = 'IDR'
),
longest_account AS (
    -- 筛选出长度最长的基准账户
    SELECT account
    FROM account_amounts
    ORDER BY LENGTH(account) DESC
    LIMIT 1
)
-- 汇总基准账户的收支总额
SELECT
    aa.日期,
    la.account AS `sl/account`,
    SUM(aa.debet_amount) AS debet,
    SUM(aa.credit_amount) AS credit,
    aa.ket
FROM account_amounts aa
JOIN longest_account la ON aa.account = la.account
GROUP BY aa.日期, la.account, aa.ket;

代码逻辑解释 #

1. account_amounts CTE：把每条交易拆成两条记录，分别记录借方账户的支出金额、贷方账户的收入金额，同时过滤掉非目标用户和非IDR的交易。
2. longest_account CTE：从所有涉及的账户中，按字符长度倒序排序，取第一条作为合并基准账户。
3. 最终汇总：关联基准账户，对该账户的所有收支金额求和，按日期、账户、交易状态分组得到结果。

注意：将代码中的你的交易表名替换为实际的表名称。

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