当然，这还没有经过测试--假设我正在尝试它，但您必须让我知道它是如何工作的。

要点：去掉相关子查询，改用SUM/GROUP BY。

应该有3种情况可能涵盖所有情况，也可能不涵盖所有情况(我认为它们应该涵盖所有情况，但您可以通过将我的内部联接更改为左联接来测试该理论，并查看左侧是否有空结果)。

显然，索引可能会有很大帮助。例如store_tras_id上索引(包括accting_trans_id和store_mount)

我不知道表中有什么(如果)主键，所以在我有c.PK <> c2.PK的查询中，这意味着表上的主键-尽管在这种情况下，条件可能也可以省略，因为总和仍然是0或非零，这是我们所关心的。

select c.*, c2.[Comparison]
from compare c
inner /* left */ join
(
    -- case 1: acctg_trans_id is not null
    select 
        store_trans_id,
        'Match' as [Comparison]
    from compare c
    where 
        store_trans_id is not null 
        and acctng_trans_id is not null
    union all
    -- case 2: acctg_trans_id is null and two store amounts sum to 0
    select 
        c.store_trans_id,
        'Store item transaction reversed' as [Comparison]
    from 
        compare c
        inner join compare c2
        on c.store_trans_id = c2.store_trans_id
        and c.PK <> c2.PK
    where 
        c.acctng_trans_id is null
        having sum(c.store_amount) = 0
    union all
    -- case 3: acctg_trans_id is null and two store amounts do not sum to 0
    select 
        c.store_trans_id,
        'Research further' as [Comparison]
    from 
        compare c
        inner join compare c2
        on c.store_trans_id = c2.store_trans_id
        and c.PK <> c2.PK
    where 
        c.acctng_trans_id is null
    having 
        sum(c.store_amount) <> 0
) c2
on c.store_trans_id = c2.store_trans_id