如何筛选AccountTransaction表中Transaction_ID唯一及对应标识总和不等的交易

问题1：筛选Transaction_ID仅出现一次的记录（如Sally的交易） #

附加问题：筛选同一Transaction_ID下，Is_Corresponding_Transaction为0的金额总和不等于为1的金额总和的记录（如Ted的交易） #

你尝试的SQL代码逻辑有问题：NOT EXISTS子句里直接用HAVING COUNT(*)>1不生效，因为HAVING必须配合GROUP BY才能正确统计分组数量。以下是可行的解决方案：

问题1解决方案 #

方法1：子查询+分组统计

SELECT 
    u.Full_Name, 
    a.Amount, 
    a.Posted_Date, 
    a.Payee_Name, 
    a.Memo, 
    ac.Account_Name
FROM AccountTransaction a
LEFT JOIN Accounts ac ON ac.Account_Code = a.Account_Code
LEFT JOIN users u ON a.UserId = u.UserId
WHERE a.Pending = 0
AND EXISTS (
    SELECT 1 
    FROM AccountTransaction b 
    WHERE b.Transaction_ID = a.Transaction_ID
    GROUP BY b.Transaction_ID
    HAVING COUNT(*) = 1
)
ORDER BY a.Posted_Date DESC;

方法2：窗口函数（更高效）

SELECT 
    Full_Name, 
    Amount, 
    Posted_Date, 
    Payee_Name, 
    Memo, 
    Account_Name
FROM (
    SELECT 
        u.Full_Name, 
        a.Amount, 
        a.Posted_Date, 
        a.Payee_Name, 
        a.Memo, 
        ac.Account_Name,
        COUNT(*) OVER (PARTITION BY a.Transaction_ID) AS trans_count
    FROM AccountTransaction a
    LEFT JOIN Accounts ac ON ac.Account_Code = a.Account_Code
    LEFT JOIN users u ON a.UserId = u.UserId
    WHERE a.Pending = 0
) AS sub
WHERE trans_count = 1
ORDER BY Posted_Date DESC;

附加问题解决方案 #

方法1：子查询+分组求和对比

SELECT 
    u.Full_Name, 
    a.Amount, 
    a.Posted_Date, 
    a.Payee_Name, 
    a.Memo, 
    ac.Account_Name
FROM AccountTransaction a
LEFT JOIN Accounts ac ON ac.Account_Code = a.Account_Code
LEFT JOIN users u ON a.UserId = u.UserId
WHERE a.Pending = 0
AND EXISTS (
    SELECT 1
    FROM AccountTransaction b
    WHERE b.Transaction_ID = a.Transaction_ID
    GROUP BY b.Transaction_ID
    HAVING 
        SUM(CASE WHEN b.Is_Corresponding_Transaction = 1 THEN b.Amount ELSE 0 END) 
        != 
        SUM(CASE WHEN b.Is_Corresponding_Transaction = 0 THEN b.Amount ELSE 0 END)
)
ORDER BY a.Posted_Date DESC;

方法2：窗口函数提前计算总和

SELECT 
    Full_Name, 
    Amount, 
    Posted_Date, 
    Payee_Name, 
    Memo, 
    Account_Name
FROM (
    SELECT 
        u.Full_Name, 
        a.Amount, 
        a.Posted_Date, 
        a.Payee_Name, 
        a.Memo, 
        ac.Account_Name,
        SUM(CASE WHEN a.Is_Corresponding_Transaction = 1 THEN a.Amount ELSE 0 END) OVER (PARTITION BY a.Transaction_ID) AS sum_1,
        SUM(CASE WHEN a.Is_Corresponding_Transaction = 0 THEN a.Amount ELSE 0 END) OVER (PARTITION BY a.Transaction_ID) AS sum_0
    FROM AccountTransaction a
    LEFT JOIN Accounts ac ON ac.Account_Code = a.Account_Code
    LEFT JOIN users u ON a.UserId = u.UserId
    WHERE a.Pending = 0
) AS sub
WHERE sum_1 != sum_0
ORDER BY Posted_Date DESC;

内容的提问来源于stack exchange，提问作者shelbyKiraM-RIO