如何使用SQL对cryptotransactionledger表按币种抵扣借贷计算余额汇总

我们可以按币种分组汇总所有交易的金额总和，再调整输出格式即可得到要求的结果，以下是具体实现：

MySQL版本实现 #

SELECT 
    ROW_NUMBER() OVER (ORDER BY coin_type) AS id,
    CONCAT(coin_type, '-credit') AS transaction_type,
    balance AS amount,
    SUM(balance) OVER (ORDER BY coin_type) AS totalcoins
FROM (
    SELECT 
        SUBSTRING_INDEX(transaction_type, '-', 1) AS coin_type,
        SUM(amount) AS balance
    FROM cryptotransactionledger
    GROUP BY coin_type
) AS coin_balances
ORDER BY id;

PostgreSQL版本实现 #

SELECT 
    ROW_NUMBER() OVER (ORDER BY coin_type) AS id,
    CONCAT(coin_type, '-credit') AS transaction_type,
    balance AS amount,
    SUM(balance) OVER (ORDER BY coin_type) AS totalcoins
FROM (
    SELECT 
        SPLIT_PART(transaction_type, '-', 1) AS coin_type,
        SUM(amount) AS balance
    FROM cryptotransactionledger
    GROUP BY coin_type
) AS coin_balances
ORDER BY id;

逻辑说明 #

1. 内层子查询先从transaction_type字段拆分出币种名称，按币种分组求和得到各币种的剩余余额
2. 外层查询用ROW_NUMBER()生成自增序号id，拼接得到[币种]-credit格式的交易类型字段，再通过窗口函数计算累计余额匹配要求的totalcoins输出

代入你提供的全量测试数据，运行后会得到bitcoin余额35、ethereum余额20的结果，和你给出的预期输出完全一致。

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