SQL查询需求：重复交易记录仅保留首个值，其余置0

现有TestGeneralTransY表存在重复行，需编写SQL查询实现：新增MInAmount列，每组重复交易记录仅首行保留ACCOUNTINGCURRENCYAMOUNT值，其余行置0，以满足求和计算需求。

当前尝试的查询未达到预期效果（例如PAF-004644308的两行中，需一行显示-79.5，另一行显示0），原查询代码如下：

SELECT         JOURNALNUMBER, SUBLEDGERVOUCHER, ACCOUNTINGCURRENCYAMOUNT, MAINACCOUNTVALUE, 
                    ITEMID,  QTY, INVENTTRANSID, 
                         (SELECT        count(journalnumber)
                           FROM            TestGeneralTransY AS tab
                           WHERE        (TestGeneralTransY.JOURNALNUMBER = JOURNALNUMBER) AND (TestGeneralTransY.ITEMID = ITEMID)
                           AND (TestGeneralTransY.mainaccountvalue = mainaccountvalue) AND (TestGeneralTransY.ACCOUNTINGCURRENCYAMOUNT = ACCOUNTINGCURRENCYAMOUNT)
                           ) AS MInAmount
FROM            TestGeneralTransY

原查询通过子查询计数的方式无法区分每组内的行顺序，无法实现“仅首行保留金额”的需求。可以利用窗口函数给每组内的行分配序号，再根据序号判断是否保留原金额。

适配大部分现代数据库（SQL Server、MySQL 8.0+、PostgreSQL等）的写法： #

SELECT 
    JOURNALNUMBER,
    SUBLEDGERVOUCHER,
    ACCOUNTINGCURRENCYAMOUNT,
    MAINACCOUNTVALUE,
    ITEMID,
    QTY,
    INVENTTRANSID,
    CASE 
        WHEN ROW_NUMBER() OVER (
            PARTITION BY JOURNALNUMBER, ITEMID, MAINACCOUNTVALUE, ACCOUNTINGCURRENCYAMOUNT 
            ORDER BY (SELECT NULL) -- 若有明确排序字段（如交易时间、主键ID），替换此处以保证结果稳定
        ) = 1 THEN ACCOUNTINGCURRENCYAMOUNT 
        ELSE 0 
    END AS MInAmount
FROM TestGeneralTransY;

关键说明： #

• PARTITION BY后的字段是分组依据，即哪些字段相同视为一组重复记录，可根据实际业务场景调整；
• ORDER BY用于定义每组内“首行”的判定规则，若有业务上的顺序字段（如TRANSACTIONDATE、表主键），建议替换(SELECT NULL)，避免结果随机；
• ROW_NUMBER()函数会给每组内的行分配从1开始的连续序号，序号为1的行保留原金额，其余行置0。

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